Power Automate: formatDateTime Function

The formatDateTime Function allows you to turn a specific date in any format that you want. It’s quite useful since different countries represent dates differently. Another excellent usage for this is to fetch a string that has a specific format and convert it in 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 in this current moment.

Usage

It follows a simple pattern.

1. Date
2. Format

Example:

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

will return 

2020-02-05

Please be aware that 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 quite more 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 of the components.

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 that you want.

Limitations

Contrary to SharePoint and PowerApps, there’s no limitation to the date that 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 and not a date. It can be tricky because in some cultures they 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 be converting dates incorrectly.
2. Only represent the values that you want to display to the user. For example, if you don’t need the hour, minute, and second remove it 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 full control of the stored value  since different systems store missing information differently. You may have systems that have the default time as noon instead of midnight.
4. Use this function only to format dates to display to the users or convert it into a format that other systems can understand. Don’t try to use 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