Date formatting

[aggneha9821]

Hi , below is my use case

Suppose today is 2may2024 .I have to date formatting like - last day of month i.e 31may2024 minus 60 days . 

I need to fetch the date last date of month minus 60days . Could anyone tell how to do this.

[MichaelAnnis · Accepted solution]

Using date practices here:
https://www.linkedin.com/pulse/power-automate-desktop-pad-best-practices-part-1-date-michael-annis/?...

2may2024 to date where 2may2024 was text format dMMMMyyyy

convert date to text MMM as month = May

convert date to text yyyy as year = 2024

set variable as text 01monthyear = 01May2024

convert to date 01MMMyyyy = 01May2024 (as FDTM; first day this month as date)

now use date functions to calculate what you need

FDTM + 1 month = 01Jun2024 to FDNM (First day next month)

FDNM - 1 day = 31May2024 or LDTM (Last day this month)

LDTM - 60 days = Target date

Good luck!

[Deenuji · Accepted solution]

@aggneha9821 

Please follow the below approach:

1. Set the Date String: Create string variable Strdate to the value 4mar2024, representing the date "24th March 2024".

2. Convert Text to DateTime: The code then converts the text representation of the date stored in Strdate to a DateTime format using the this function. The converted DateTime value is stored in the variable ConvertedDateTime.

3. Convert DateTime to Custom Text Format: Next, the code converts the DateTime value stored in ConvertedDateTime to a custom text format using this function. The custom format specified is dd, which represents the day of the month. The resulting formatted date text is stored in the variable FormattedDateTime.

4. Convert Text to Number: The code then converts the text representation of the formatted date stored in FormattedDateTime to a number using this function. The resulting number is stored in the variable TextAsNumber.

5. Calculate First Date of Previous Month: After converting the text to a number, the code subtracts the number of days represented by TextAsNumber minus one from the original date (ConvertedDateTime) using the DateTime.Add function. This calculates the first date of the previous month and stores it in the variable Firstdate.

6. Calculate Last Date of Current Month: The code then adds one month to the first date of the previous month (Firstdate) using the DateTime.Add function, resulting in the last date of the current month, stored in the variable LastDate.

7. Calculate Last Day of Current Month: The code subtracts one day from the last date of the current month (LastDate) using the DateTime.Add function to get the last day of the current month, stored in the variable LastDayInMonth.

8. Calculate Final Date: Finally, the code subtracts 60 days from the last date of the current month (LastDate) using the DateTime.Add function, resulting in the final date, stored in the variable FinalDate.

Code:

SET Strdate TO $'''24mar2024'''
Text.ConvertTextToDateTime.ToDateTime Text: Strdate DateTime=> ConvertedDateTime
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: ConvertedDateTime CustomFormat: $'''dd''' Result=> FormattedDateTime
Text.ToNumber Text: FormattedDateTime Number=> TextAsNumber
DateTime.Add DateTime: ConvertedDateTime TimeToAdd: $'''-%TextAsNumber - 1%''' TimeUnit: DateTime.TimeUnit.Days ResultedDate=> Firstdate
DateTime.Add DateTime: Firstdate TimeToAdd: 1 TimeUnit: DateTime.TimeUnit.Months ResultedDate=> LastDate
DateTime.Add DateTime: LastDate TimeToAdd: -1 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> LastDayInMonth
DateTime.Add DateTime: LastDate TimeToAdd: -60 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> FinalDate
Display.ShowMessageDialog.ShowMessage Message: $'''New Date : %FinalDate%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

How to copy/paste the above code into your power automate desktop?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀