How to format date and time in a readable way in Power Automate

“When I add date and time from SharePoint into a Power Automate email, it’s displayed as 2020-09-14T17:00:00Z, can I display the date in more readable format?”

“The date from SharePoint is stored as 2020-09-14T13:22:12Z into Excel file, it’s not ideal as I need to filter on this columns.”


The date/time format like ‘2020-09-14T16:08:59Z’ is the standard SharePoint uses to store the date/time value. When you use the date/time column directly in the Power Automate flow, it’ll take the value as it is and display it in this not-so-pretty format. That’s why there’s an expression to display it in a more readable/usable format: formatDateTime().

formatDateTime(<date>, '<format>')

Given the value above is the Created date/time and we want to keep only the date part (2020-09-14), it could look like this:

formatDateTime(triggerOutputs()?['body/Created'], 'yyyy-MM-dd')

For the <date> part you can use any date/time column, even a string column as when saving date from MS Forms.

For the <format> there’re a lot of options (all of them being documented), but I want to concentrate on the ones I consider the most useful and where you have full control over the format. Just remember that the <format> must be inside single quotes as seen in the examples above.

Date format options

For each part of the date/time column you can define a format, so you’ve got format for year, month, day, hours, minutes and seconds, and you can combine them together as you like.

Let’s take an example date/time value ‘2020-09-14T16:08:59Z’ and split it into formatted parts to see what each of them looks like.

Year

Formatyyyyyyyyyy
Display value20200202020

Month

FormatMMMMMMMMMM
Display value909SepSeptember

Day

Formatdddddddddd
Display value1414MonMonday

Hour

Formath (tt)hh (tt)H HH
Display value4 (PM)04 (PM)1616

Minute

Formatmmm
Display value808

Second

Formatsss
Display value5959

As you can see, often the number of characters used in the format defines the minimum number of characters that’ll be displayed for given value, e.g. ‘y’ will show ‘1’, ‘yy’ will show ’01’ and ‘yyy’ ‘001’.

Building the date

You can use any combination of the formats from above, add your preferred separators between the pieces, and define which part of the date/time value you want to display and how.

It’s not necessary to use the whole date/time value, the formatDateTime() expression enables you to display only the information that’s relevant to you and your solution.

I’ll finish this post with some examples I recently used, <date> always being ‘2020-09-14T16:08:59Z’. You can see that I never use seconds, and very often not even the hours and minutes (especially when comparing dates the hours and minutes cause problems).

formatDateTime(<date>, 'yyyy/MM/dd')
2020/09/14

formatDateTime(<date>, 'yyyy-MM-dd HH:mm')
2020-09-14 16:08

formatDateTime(<date>, 'MMMM dd, yyyy')
September 14, 2020

formatDateTime(<date>, 'dddd, MMMM dd, yyyy h:mm tt')
Monday, September 14, 2020 4:08 PM

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to a special content like a SharePoint Filter Query cheat sheet.

Zero spam, unsubscribe anytime.

Add a Comment

Your email address will not be published. Required fields are marked *