FormatDateTime and Formatting a String Date

You’ve got a date for your Power Automate from a source whether that be a list item, dataverse entity attribute or other source and you want to compare it against today’s date or a date in the past/future, what options are available to you to manipulate that date? Maybe you are getting an error “The datetime string must match ISO 8601 format” with a date string you have provided an action, what does that mean? You can read up about ISO 8601 here but ultimately it means [YYYY]-[MM]-[DD], I explain how to repurpose your date time string below – click here

But Wait!! In Feburary 2022 Microsoft released a new expression ParseDateTime which will allow you to define the locale of your source string. I show you how to use this in my latest video below, but please still read on if you want more information about manipulating date times in Power Automate.

So what options do you have in Power Automate to manipulate a date?

Actions

Power Automate currently has 6 available actions that allows you to conveniently play with your time:

1. add (or subtract if the interval is negative) a unit of time from any date/time string that you provide as long as it is in the correct format. You choose the interval and unit whether that be month, week, day, hour, minute or second.

2. convert timezone from a base date/time string from a source timezone i.e. UTC and destination i.e. UTC -08:00 Pacific Time (US & Canada) and choose the default format string.

3. current time – which as it sounds is the current time in UTC. What is UTC? Is it BST? Click here to find out more.

4. get future time (or past if the interval is negative) is a time in the future from the moment the action is run and you can specificy the interval and unit whether that be month, week, day, hour, minute or second.

5. get past time (or future if the interval is negative) is a time in the past from the moment the action is run and you can specificy the interval and unit whether that be month, week, day, hour, minute or second.

6. subtract from time (or add if the interval is negative) a unit of time from any date/time string that you provide as long as it is in the correct format. You choose the interval and unit whether that be month, week, day, hour, minute or second.

Expressions

Expressions can be used within actions, variables and composes in order to format or manipulate your string. This is the low code that Microsoft refers to.

utcnow() will return the current date time in ISO 8601 format

formatdatetime() will take an ISO 8601 timestamp string and convert it using the supplied format string. Most common examples as ‘yyyy-MM-dd’ or ‘dd-MM-yyyy’ remember that month is represented by uppercase MM where minutes are lower mm for example ‘hh:mm:ss’. This expression is useful for creating unique strings for filenames for instance ‘yyyyMMddhhmmss’. Days and Months can also be respresented by ‘ddd’ for Mon or ‘dddd’ for Monday, ‘MMM’ for Jan or ‘MMMM’ for January and when it comes to time, lowercase ‘hh’ for 12 hour and uppercase ‘HH’ for 24 hour! More examples available here. Remember if you receive the error “The datetime string must match ISO 8601 format“ the string you have provided the expression is not in the expected yyyy-MM-dd format.

adddays() will take an ISO 8601 timestamp string, an integer number of days to add and a date time format and will return a date in the future or in the past if a negative integer is supplied.

dayofmonth() will take an ISO 8601 timestamp string and will return an int to represent the ‘d’ of the month i.e. 2021/04/02 would return 2.

dayofweek() will take an ISO 8601 timestamp string and will return an int to represent the day of the week 1-7, Friday would return 5.

dayofyear() will take an ISO 8601 timestamp string and will return an int to represent the number of days that have elapsed in the year since 1st January.

startofday() will take an ISO 8601 timestamp string and will return the date time with 00:00:00 as the time element

startofhour() will take an ISO 8601 timestamp string and will return the date time with the time element to the current hour i.e. 16:40:43 would return 16:00:00

startofmonth() will take an ISO 8601 timestamp string and will return the date time with the date element set to the 1st of the month and 00:00:00 as the time element

Now what about that ERROR? “The datetime string must match ISO 8601 format”

In laymans, the string you have provided the expression or action is not in the expected yyyy-MM-dd format. The easiest way for you to check the format of the string you have provided the solution is via the Flow history but if it isn’t obvious try inserting a compose action with your dynamic value and then re-run the flow and check the history of the compose.

Assuming your date is straight out of another source, how can you reformat is so that it is accepted? Let me show you below with two different methods.

OPTION 1 If you are familiar with arrays, you can split the string by ‘-‘ and reformat the string by calling the elements of the array as follows:

The results of these actions are as follows:

OPTION 2 If you would rather play with strings and substrings you can use the expression substring and obtain the individual parts of the dd MM and yyyy and reorder them. Note that I have used seperate compose actions to do this, you could complete this in one compose action to keep things a little bit neater and compact.

I’ve then updated the original Add to Time action with my newly formatted Reformat2 compose action and it has added 2 months onto my date/time. The date is now in the ISO 8601 format and I do not get the error “The datetime string must match ISO 8601 format”

Well, I hope this article saves you some time on the next day that you try and format your dates and times in Power Automate.

Please leave your comments and thoughts below, have I missed an expression or action?

Want to learn more about Power Automate or read up on some of my business solution ideas? Read more here.

Tags: Date ErrorDate ExpressionsFormatDateTimeFormatting a Date in Power AutomateISO 8601 FormatPower Automate