Simplify Date Operations using Power Fx Functions in Power Automate Desktop

[Deenuji]

Have you had a chance to review the latest Power Automate Desktop March 2024 released features?

Microsoft has introduced a Power Fx function helper, featuring multiple formulas designed to enhance our day-to-day operations. This blog is divided into multiple parts to cover the extensive range of formulas available. Today, we'll focus on exploring the functionality of various date formulas within Power Automate Desktop.

1. Date - Converting Numbers into Dates

In Power Automate Desktop, you can now effortlessly convert numbers into dates using Power Fx forumla, which features a handy formula named "Date" designed specifically for this task.

Flow Screenshot:

Explanation:

For instance, if you wish to express date Example: (2024,03,14) as a number, you can simply utilize the Date formula in Power Fx to make the conversion.

Code:

# #Return Numbers as Date Using Power Fx Date function
SET Return_Number_AsDate TO $fx'${Date(2024,03,14)}'
Display.ShowMessageDialog.ShowMessage Title: $fx'Converted Date' Message: $fx'Converted Date from number is : ${Return_Number_AsDate}' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

2. DateAdd - Adding and Subtracting Units from Dates:

With the DateAdd function, you can effortlessly manipulate dates by adding or subtracting a specified number of units. This allows for dynamic date calculations within your automation flows.

Flow Screenshot:

Explanation:
Here, CurrentDate is initialized to March 16, 2024. Then, the DateAdd function is used to add 45 units to CurrentDate and store the result in AddDate. Similarly, it subtracts 45 units from CurrentDate and stores the result in SubDate.

Code:

# #The DateAdd function adds a number of units to a date/time value.
SET CurrentDate TO $fx'=Date(2024,03,16)'
SET AddDate TO $fx'=DateAdd(CurrentDate,45)'
SET SubDate TO $fx'=DateAdd(CurrentDate,-45)'
Display.ShowMessageDialog.ShowMessage Title: $fx'="Info"' Message: $fx'="Add Date Result:" & AddDate &"Sub Date Result:" & SubDate' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

3. DateDiff - Calculating Date Differences

Another common scenario involves determining the difference between two dates. Here's how you can achieve this using Power Fx.

Utilize the "DateDiff" formula. Simply add the start date and end date as parameters, as shown in the screenshot below. This will yield the difference in results as a number.

Flow Screenshot:

Explanation:

In this code snippet, the DateDiff function calculates the difference between StartDate (March 14, 2024) and EndDate (February 11, 2025) in terms of days. The resulting difference is then displayed in a message dialog, providing valuable insights into the temporal gap between the two dates.

Code:

#The DateDiff function returns the difference between two date/time values. The result is a whole number of units.
SET StartDate TO $fx'03/14/2024'
SET EndDate TO $fx'02/11/2025'
SET Result_DateDifference TO $fx'=DateDiff(StartDate,EndDate)'
Display.ShowMessageDialog.ShowMessage Title: $fx'Date Difference Result' Message: $fx'Date Difference Result: ${Result_DateDifference}' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

4. DateTime - Converting Numbers to Date and Time

The DateTime function in Power Fx allows you to convert individual year, month, day, hour, minute, and second values into a date and time format. Here's how you can use it:

Flow Screenshot:

Explanation:

In this example, the DateTime function is used to convert the individual values representing the year (2024), month (03), day (16), hour (21), minute (05), and second (31) into a date and time format. 

Code:

# #The Date function converts individual Year, Month, and Day values to a Date Time. 
SET ConvertedDateTime TO $fx'=DateTime(2024,03,16,21,05,31)'
Display.ShowMessageDialog.ShowMessage Title: $fx'="Info"' Message: $fx'=ConvertedDateTime' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

5. DateTimeValue- Localization in Date and Time Conversion:

Power Fx also supports localization, allowing you to convert dates and times according to specific locales. You can achieve this using the DateTimeValue function.

Flow Screenshot:

Explanation: 

In this example, the DateTimeValue function is used to convert the provided date and time string "January 10, 2013 12:13 AM" into a date and time format. The first conversion considers the locale as "fr" (French), while the second conversion utilizes the long date format based on the user's locale. 

Code:

# #The Date function converts individual Year, Month, and Day values to a Date Time in the current locale or user's locale.
SET ConvertedFrlocaleDateTimeValue TO $fx'=DateTimeValue("January 10, 2013 12:13 AM","fr")
'
SET ConvertedUserlocaleDatetimevalue TO $fx'=DateTimeValue("January 10, 2013 12:13 AM",DateTimeFormat.LongDate)
'
Display.ShowMessageDialog.ShowMessage Title: $fx'="Info"' Message: $fx'=ConvertedFrlocaleDateTimeValue' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

6. DateValue- Localization in Date Conversion:

If you have date values represented as strings, you can utilize the DateValue function to convert them into dates, respecting the specified user locales date format:

Flow Screenshot:

Explanation: Here, the DateValue function converts the provided date string "January 10, 2013" into a date format with user specified format. 

Code:

# #The Date value function use for Convert a date from a string in the user's locale and show the result as a long date.
SET ConvertedlocaleDate TO $fx'=DateValue("January 10, 2013",DateTimeFormat.ShortDate)
'
Display.ShowMessageDialog.ShowMessage Title: $fx'="Info"' Message: $fx'=ConvertedlocaleDate' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

By incorporating these date-related functions into your Power Automate Desktop workflows, you can streamline processes, enhance accuracy, and boost productivity. Whether you're converting dates, calculating differences, or performing dynamic date calculations, Power Fx empowers you to handle date and time operations with ease, making automation tasks more efficient and effective. Start leveraging the power of Power Fx for your date-related automation needs today!