Skip to content

Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Problems
  • Triggers
  • Application specific solutions
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Resources
Menu

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

Posted on September 16, 2020January 2, 2022 by Tom

“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

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

Zero spam, unsubscribe anytime.

Leave a Reply Cancel reply

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

NOW AVAILABLE:

The Ultimate Power Automate expressions cheat sheet
Spend your time thinking about what the flow should do, not how to do it!

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.

Zero spam, unsubscribe anytime.

Hello and welcome!

My name is Tom and I'm a business process automation consultant and Microsoft MVP living in the Czech Republic. I’ve been working with Microsoft technologies for almost 10 years, currently using mainly Power Automate, SharePoint, Teams, and the other M365 tools.

I believe that everyone can automate part of their work with the Power Automate platform. You can achieve a lot by "clicking" the flows in the designer, but you can achieve much more if you add a bit of coding knowledge. And that's what this blog is about.

To make the step from no-code Power Automate flows to low-code flows: using basic coding knowledge to build more complex yet more efficient flows to automate more of your daily tasks.

  • How to create a new SharePoint list column with Power Automate flowMay 22, 2022
  • How to combine expressions in your Power Automate flowsMay 18, 2022
  • Import Planner tasks with checklists into various buckets (Power Automate)May 15, 2022
  • How to get notified when Planner task was reassigned (Power Automate)May 11, 2022
  • How to extract value from XML using Power Automate flowMay 8, 2022

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

© 2022 Let's POWER Automate | Powered by Superbs Personal Blog theme