Power Automate: formatDateTime Function

The formatDateTime Function allows you to turn a specific date in any format you want. It’s pretty helpful since different countries represent dates differently. Another excellent usage for this is to fetch a string that has a specific format and convert it into another that you can store in your database or SharePoint list. The utcNow() function works similarly, but it doesn’t require the first field since you’re already asking for the date at this current moment.

Usage

It follows a simple pattern.

1. Date
2. Format
3. Locale (optional)

Example:

formatDateTime('2020-02-05T10:10:00Z','yyyy-MM-dd')

will return 

2020-02-05

Please be aware that in the reference material, Microsoft names objects like ‘2019-10-28T10:10:00Z’ as timestamps. I prefer calling them “Date” to avoid confusion with the UNIX timestamp, commonly used in APIs, to represent the number of seconds elapsed since Jan 01, 1970 (UTC).

Microsoft has an excellent reference for what parameters you can include in the format, and I recommend you check it when you’re thinking of formatting a date. You have the option to define single format specifiers and custom format patterns.

Single format specifiers

These are pre-defined specifiers that always convert the date the same way. For example, “o” will transform into the format “yyyy-MM-ddTHH:mm:ss:fffffffK.” You can achieve the same by using the custom format pattern, but it’s pretty natural to type “o.”

formatDateTime('2020-02-05T10:10:00Z','o')

will return 

2020-02-05T10:10:00.0000000Z

Custom format patterns

With these, you can customize each value, including trailing zeros in each component.

formatDateTime('2020-02-05T10:10:00Z','ddd MM yyyy')

will return 

Wed 02 2020

The reference will have all available parts, so you can check it out and build the dates you want.

Limitations

Contrary to SharePoint and PowerApps, there’s no limitation to the date you can use. You can do things like:

formatDateTime('1000-12-30T00:00:00Z','yyyy-MM-dd')

And you'll get 

1000-12-30

Recommendations:

1. Please note that the first parameter is a string, not a date. It can be tricky because some cultures switch the month and when they represent a date, like in the USA, for example. Be sure that each component of the date is what you expect. Otherwise, you can convert dates incorrectly.
2. Only represent the values you want to display to the user. For example, if you don’t need the hour, minute, and second remove them to avoid confusion.
3. If you’re storing the returned value from the formatDateTime Function, please always include the date, even if it’s not present in the initial string. With this, you have complete control of the stored value since different systems store missing information differently. You may have systems with the default time as noon instead of midnight.
4. Use this function only to format dates to display to the users or convert them into a format that other systems can understand. Don’t try to use it to fetch individual items to do operations like adding days since there are better-suited functions to do that.
5. To get the best results, please be sure that you’re passing a date that is UTC. If you’re storing somewhere your local date, use the “Convert Time Zone” action to convert it before providing the value to the formatDateTime Function. If you don’t do it, you may be returning invalid values to the end-user.

Sources:

Microsoft’s formatDateTime Function Reference

Back to the Power Automate Function Reference.

Photo by Aron Visuals on Unsplash