“I’ve got a date in the format MMddyyyy and I’d like to convert it into dd/MM/yyyy, how can I do that in Power Automate?”
The standard date format used in Power Automate is ISO 8601. You get dates in this format, use it as a basis when formatting the date, and often also to save them. But not all systems provide the date in the desired format. Since Power Automate can process inputs from various sources, the date format can vary a lot. You might receive attachments with timestamp as MMddyyyy, while the users expect that the date will be dd/MM/yyyy. How do you convert the date? Would you try to do some combination of many expressions to split the date and put it back together? Or do you prefer a simple solution with just 2 expressions?
Convert the date into ISO date
Conversion of a custom formatted date into another custom formatted date will take two steps. Firstly, convert the custom date into an ISO date. Secondly, take the ISO date and convert it into the desired format.
The easiest way to convert any date into an ISO date is to use the parseDateTime(…) expression. Let’s take the MMddyyyy date 05212022 as an example:
parseDateTime('05212022', 'en-US', 'MMddyyyy')…and convert the date into an ISO formatted date.
2022-05-21T00:00:00.0000000Convert the ISO date into different format
The ISO date itself is not pretty enough to show it to the users, but it’s the standard format for a flow. As such you can use the formatDateTime(…) expression to format it. The expression below:
formatDateTime('2022-05-21T00:00:00.0000000', 'dd/MM/yyyy')…will format the date as:
21/05/2022Combine the expressions
Now you can do it all in a single step by combining the two expressions.
formatDateTime(parseDateTime(<theDate>, <locale>, <sourceFormat>), <targetFormat>)Note: <…> are placeholders, replace them including the < and >.
Following with the example from above:
formatDateTime(parseDateTime('05212022', 'en-US', 'MMddyyyy'), 'dd/MM/yyyy')
Summary
If you need to convert a date into another format in your Power Automate flow, you should consider using a middle step. Not trying to “brute force” the conversion, but converting it into another data format that’s easier to work with. In this case it was the conversion into ISO date that made the formatting much simpler, in other situations it can be splitting a string into an array to skip some parts of it.
Hi, Any way Formatting the day number in a date with st, nd, rd, th
For numbers that end in 1 – we add st (eg 1st, pronounced first)
For numbers that end in 2 – we add nd (eg 22nd, pronounced twenty-second)
For numbers that end in 3 – we add nd (eg 43rd, pronounced forty-third)
For all other numbers – we add th (eg 5th, pronounced fifth)
Hello Rakesh,
I never did that, but you could probably do it with multiple if(…) conditions, something like:
if(endsWith(‘date’,1), concat(‘date’,’st’), if(endsWith(‘date’,2), concat(‘date’, ‘nd’), if(endsWith(‘date’, 3), concat(‘date’, ‘rd’), concat(‘date’, ‘th’))))
Hello Tom,
I’m struggling with converting the date format from a list when I am putting it into a table to send as an email.
In the list the date is stored yyyy-mm-dd and displayed in the easy format dd/mm/yyy. However, when this gets sent in an email it reverts to yyyy-mm-dd even though I have used the following expression in a ‘Compose’ item.
formatDateTime(parseDateTime(‘2024-01-28’, ‘en-UK’, ‘yyyy-mm-dd’), ‘dd-mm-yyyy’). The flow is this:
1 – Recurrence
2 – Get Items
3 – Filter (get items = to or greater than today’s date)
4 – Compose (the above expression)
5 – Create HTML table of data from Get Items
6 – Compose (Create a nicely formatted table)
7 – Create and send email.
I’ve tried line 4 above in various positions, but the table sent always displays the date as yyyy-mm-dd.
I’m not sure what I am missing here.
Thank you.
Hello Andrew,
when using these batch actions like ‘Create HTML table’, you must do all the operations in that action. That means ‘Compose’ won’t help you if you’re the referencing only the output from ‘Get items’ when creating the action as shown in example here: https://tomriha.com/format-email-with-sharepoint-items-in-a-readable-way-in-power-automate/