DateTime format error

[JC2892]

Hello,

We are building a flow that accesses information in an excel spreadsheet hosted on Teams using "list rows present in a spreadsheet". The DateTime Format selector is set as ISO 8601. We are then running a condition to filter one of the excel columns followed by a send an email (V2) action in the 'yes' bucket.

We are using dynamic content to populate some of the email content with the information taken from the Excel. One of the pieces of information we are pulling through is the date. To format the date as we would like it we are using the expression: 

formatDateTime(items('Apply_to_each')?['Date of the Event'],'dd-MM-yyyy').

When we test the flow we are getting the error: 

Unable to process template language expressions in action 'Send_an_email_(V2)' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '30/07/2022' was not valid. The datetime string must match ISO 8601 format.'.

The excel is populated by another Power Automate and the DateTime format for excel on that other flow is set to ISO 8601 as well as on this flow.

We are doing the same thing successfully on other flows so I am stuck as to why this one is not working.

Thank you in advance!!

[divya-akula]

@JC2892  screenshots of the data and automate , please.

[JC2892]

[v-qiaqi-msft · Accepted solution]

Hi @JC2892,

This is an issue related to date format in ISO 8601.

Please modify your formula as below:

formatDateTime(addDays('01-01-1900', add(int(items('Apply_to_each')?['Date of the Event']),-2)), 'MM/dd/yyyy')

[JC2892]

Thank you SO much! We have been struggling with that for ages and your response worked. Amazing!