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, 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

---

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 resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: