Fill in variable text

[Youri98]

Hi,

I am working on an automation where I have to export data from Dynamics 365 Finance & Operations, but I have to fill in a date that changes each month to the last day of the previous month (e.g. 31-01-2024 or 29-02-2024). I have created a UI-element to select the textbox. Is there a way to create a variable function in Power Automate desktop so that it fills in the correct date each month? For now, I can only fill in hard coded text, but no dynamic ones. 

Thanks in advance!

Youri

[UshaJyothi20]

Hi @Youri98 

the below flow is to get the last day of month..

and below is the code please copy and paste in to PAD designer

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: CurrentDateTime CustomFormat: $'''ddmmyyyy''' Result=> FormattedDateTime2
SET Month TO CurrentDateTime.Month
SET Year TO CurrentDateTime.Year
Scripting.RunJavascript.RunJavascript JavascriptCode: $'''var month = %Month%;
var d = new Date(%Year%, month,0);
WScript.Echo(d);''' ScriptOutput=> JavascriptOutput ScriptError=> ScriptError
Text.ParseText.RegexParseForFirstOccurrence Text: JavascriptOutput TextToFind: $'''.+(?=\\s\\d{2}:)''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
Text.ConvertDateTimeToText.FromDateTime DateTime: Match StandardFormat: Text.WellKnownDateTimeFormat.SortableDateTime Result=> Formatteddatetime
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: Match CustomFormat: $'''dd-mm-yyyy''' Result=> FormattedDatetime
Display.ShowMessageDialog.ShowMessage Title: $'''Last Day of Current Month''' Message: FormattedDatetime Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

Hope this helps,

Usha

[Deenuji]

@Youri98 

Method1:

You can keep your month1 and month2 date in some config file like below:

And read this excel file and assign those dates into month1 and month2 variables like below and once you assigned you can call .net script(simple one) it will return the last dates of both months.

Method2: 

You can also pass your inputs months in input dialog like below

Code:

SET From_Date TO $'''%''%'''
SET To_Date TO $'''%''%'''
# Option 2: using Input Dialog
DISABLE Display.InputDialog Title: $'''From Month and Year''' Message: $'''Enter the From Month and Year(March-2024)?''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> From_Date ButtonPressed=> ButtonPressed
DISABLE Display.InputDialog Title: $'''To Date''' Message: $'''Enter the To Month and Year(March-2024)?''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> To_Date ButtonPressed=> ButtonPressed
# Option 1: using Excel config file
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\\\Documents\\Report_Date.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
LOOP FOREACH CurrentItem IN ExcelData
SET From_Date TO CurrentItem[0]
SET To_Date TO CurrentItem[1]
END
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $'''// Parse the input strings to get month and year
string[] parts1 = month1.Split(\'-\');
string[] parts2 = month2.Split(\'-\');

// Extract month and year
int monthValue1 = DateTime.ParseExact(parts1[0], \"MMMM\", System.Globalization.CultureInfo.InvariantCulture).Month;
int year1 = int.Parse(parts1[1]);

int monthValue2 = DateTime.ParseExact(parts2[0], \"MMMM\", System.Globalization.CultureInfo.InvariantCulture).Month;
int year2 = int.Parse(parts2[1]);

// Get the last day of the month
DateTime lastDayOfMonth1 = new DateTime(year1, monthValue1, DateTime.DaysInMonth(year1, monthValue1));
DateTime lastDayOfMonth2 = new DateTime(year2, monthValue2, DateTime.DaysInMonth(year2, monthValue2));

Lastdate_Month1=lastDayOfMonth1.ToString(\"dd-MM-yyyy\");
Lastdate_Month2=lastDayOfMonth2.ToString(\"dd-MM-yyyy\");''' @'name:month1': From_Date @'type:month1': $'''String''' @'direction:month1': $'''In''' @'name:month2': To_Date @'type:month2': $'''String''' @'direction:month2': $'''In''' @'name:Lastdate_Month1': $'''''' @'type:Lastdate_Month1': $'''String''' @'direction:Lastdate_Month1': $'''Out''' @'name:Lastdate_Month2': $'''''' @'type:Lastdate_Month2': $'''String''' @'direction:Lastdate_Month2': $'''Out''' @Lastdate_Month1=> From_Date @Lastdate_Month2=> To_Date
Display.ShowMessageDialog.ShowMessage Title: $'''From date/To Date''' Message: $'''From date: %From_Date%
To date: %To_Date%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed2
WebAutomation.LaunchChrome.LaunchChrome Url: $'''www.google.com''' WindowState: WebAutomation.BrowserWindowState.Normal ClearCache: False ClearCookies: False WaitForPageToLoadTimeout: 60 Timeout: 60 BrowserInstance=> Browser

Thanks,

Deenu

[Youri98]

Hi Usha,

Thank you for your reply. When I do this I get 01-00-2029 as the date.

I'll try to figure out where this is going wrong.

If you have a solution, please let me know.

Thank you!

[UshaJyothi20]

Hi @Youri98 

In display message please change the variable to match

This worked for me..as shown in the below image.

Hope this helps

Usha.

[Youri98]

Thanks, that works, but I would like to get it to show the last day of the last month. So that would be 31-01-2024. Any idea how I could get this to work?

[UshaJyothi20]

Please Let me know if that works for you

Usha

[UshaJyothi20]

Please find below image made some changes 

please copy and paste the code into the PAD designer and get back to me if you have any doubts

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateOnly CurrentDateTime=> CurrentDateTime
SET Year TO CurrentDateTime.Year
SET Month TO CurrentDateTime.Month
IF (Month = 1) = True THEN
SET LastMonth TO 12
ELSE
SET LastMonth TO Month - 1
END
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.VisualBasic ReferenceRootPath: $'''C:\\PowerAutomate\\Assembly References''' Script: $'''IN_Date = system.datetime.Daysinmonth(%Year%,%LastMonth%)''' @'name:IN_Date': $'''''' @'type:IN_Date': $'''Int''' @'direction:IN_Date': $'''Out''' @IN_Date=> LastDate
Display.ShowMessageDialog.ShowMessage Message: LastDate Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

Hope this helps

Usha.

[WillSG]

Hi @Youri98  I hope you are doing well.

Besides the other recommendations which are great btw, you can use Python to get the last day of the previous month:

See the snippet code below:

SET DateFormat1 TO $'''%'%d-%m-%Y'%'''

SET DateFormat2 TO $'''%'%m-%d-%Y'%'''

@@copilotGeneratedAction: 'False'

Scripting.RunPythonScript PythonCode: $'''from datetime import datetime, timedelta

def get_previous_month_last_date():

    today = datetime.today()

    first_day_of_current_month = today.replace(day=1)

    last_day_of_previous_month = first_day_of_current_month - timedelta(days=1)

    # Format the date as dd-mm-yyyy

    formatted_date = last_day_of_previous_month.strftime(\"%DateFormat1%\")

    return formatted_date

# Example usage

previous_month_last_date = get_previous_month_last_date()

today_date = datetime.today().strftime(\'%DateFormat2%\')

output_string = \"Today\'s date is {}, last previous month last day was {}\".format(today_date, previous_month_last_date)

#print(output_string)

print(previous_month_last_date)

''' ScriptOutput=> PythonScriptOutput

Display.ShowMessageDialog.ShowMessage Message: PythonScriptOutput Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: True ButtonPressed=> ButtonPressed

Just copy and paste, easy peasy 😉

Please let me know if it works, happy automation!,

If I have addressed your inquiry successfully, kindly consider marking my response as the preferred solution. If you found my assistance helpful, a 'Thumbs Up' would be greatly appreciated.

Additionally, I offer specialized consultancy and development services leveraging PAD. If you're interested in exploring these services further, feel free to DM me, and we can initiate a discussion.

Finally, here is my LinkedIn Profile if you want to follow or connect with me.

Kind regards,

Will SG

Managing Director & Automation Lead

RAMS CR (Recruitment & Automation)

[Agnius · Accepted solution]

No offense to the other suggestions, but the recommendations are overly complicated and quite terrible.

All you need to get the last date of the previous month formatted in a special way is really just three actions. See here:

 

The steps are as follows:

1. You get the current date via Get current date and time
2. You use Add to date time to deduct %CurrentDateTime.Day%days from %CurrentDateTime%. %CurrentDateTime.Day% will store the current day of the month as an integer - so 23 for the 23rd of February, for example. If you deduct that number of days from the current date, it will result in the last day of the previous month dynamically.
3. You use Convert date time to text to convert it into the format you need for the target application.

You can copy this code into your PAD designer to generate the three actions:

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateOnly CurrentDateTime=> CurrentDateTime
DateTime.Add DateTime: CurrentDateTime TimeToAdd: $'''-%CurrentDateTime.Day%''' TimeUnit: DateTime.TimeUnit.Days ResultedDate=> ResultedDate
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: ResultedDate CustomFormat: $'''dd-MM-yyyy''' Result=> FormattedDateTime

[Youri98]

Thank you! Indeed, thanks to everyone for trying to help, but this solution from Agnius is the easiest to use.