Format the date from excel dynamic content

[SteveMadden]

Hi all, I'm bit of a noob, but learning all the time.

Not a question, but something I thought I would share. I have spent the last 2 hours trying to figure out how to get the date generated by dynamic content from excel to show as 'dd/mm/yyyy' instead of the number excel uses (e.g. '455553') and the solution was frustratingly simple.

I wanted to send an email notification based on an Excel table which tracked items that people needed to update. In the body of the email I wanted to display the deadline date which would be retrieved from a column in the table.

I used the 'List rows present in a table' connector to get the data I wanted, used the 'For each' and email connectors to send an email for each qualifying row and composed the body of the email:

in the body of the email, I used dynamic content to identify the Item and to inform of the Date, but it would always be in the number format that excel uses (eg '455553').  

To get the date into the standard dd/mm/yyyy format (or whatever you prefer), go to the Advanced parameters of the 'List rows present in a table' connector and under the DateTime Format option, choose 'ISO 8601'  - it's that simple.

I'm sure that the more experienced members here will be well aware of this function, but I hope it helps other newbies to Power Automate 🙂

[v-qiaqi-msft · Accepted solution]

HI @SteveMadden,

You are so right that selecting the 'ISO 8601' under the DateTime Format option is a simplest way to convert date time value from Excel table.

Glad that you have solved this.

I will mark here to close this case so that users who may have the same issue will be navigated here directly.

Thanks for your cooperation.

[sandeepstw]

Hi @SteveMadden ,

To format the date from an Excel table to 'dd/mm/yyyy' in Power Automate:

1. In the 'List rows present in a table' action, go to Advanced options.
2. Set the DateTime Format to 'ISO 8601'.

This converts the date to a standard format that can be easily manipulated.

And this article: https://powerautomate.microsoft.com/en-us/blog/working-with-dates-and-times/

Mark as solution if it helps. 

Thanks, 

Sandeep