“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, you can use one of the standard formats or a fully custom format. I’ll 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
| Format | y | yy | yyy | yyyy |
| Display value | 20 | 20 | 020 | 2020 |
Month
| Format | M | MM | MMM | MMMM |
| Display value | 9 | 09 | Sep | September |
Day
| Format | d | dd | ddd | dddd |
| Display value | 14 | 14 | Mon | Monday |
Hour
| Format | h (tt) | hh (tt) | H | HH |
| Display value | 4 (PM) | 04 (PM) | 16 | 16 |
Minute
| Format | m | mm | ||
| Display value | 8 | 08 |
Second
| Format | s | ss | ||
| Display value | 59 | 59 |
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