Answered

Format Date Time Error

(1) Share

Report

Posted on by phishseng91

Hello everyone,

I have created a schedule flow to automatically send an email if the Delete Date (from an Excel file) is equals to today's date.

I have tried it on a test Excel file and it worked fine.

But once I input our working file into the automation, the same error appears again : Unable to process template language expressions for action 'Condition' at line '1' and column '16207': 'In function 'formatDateTime', the value provided for date time string '44348' was not valid. The datetime string must match ISO 8601 format.'.

This error message was solved previously by selecting the "ISO 8601" on the DateTime Format of the List rows. I am not sure why it gives this error once more.

This is how my flow looks like :

The formula i wrote for the Condition is formatDateTime(items('Apply_to_each')?['Delete Date'],'yyyy-MMM-dd') equals to formatDateTime(body('Convert_time_zone'),'yyyy-MMM-dd')

Can anyone help me, please?

Thank you!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Expiscornovus 33,474 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @phishseng91,

It looks like in one of your rows the value of the delete date column is 44348 instead of a datetime value. Can you check your delete date column in your working file, how is that formatted?

Was this reply helpful? Yes No
Verified answer

phishseng91 37 on at

Like (2)

Report
Copy link

Link copied!

Hello!

Thank you for your response!
The delete date column is formatted as date on Excel.

And we did many tryouts but for some reason Power Automate will only read it as the text 44348 instead of the date itself. When we change the cell's format to General on Excel, it shows the exact same number of 44348.

We have found the solution, though! To enable Power Automate to read the proper dates from Excel, we need to input an expression :
if(empty(item()?['Delete Date']),null,addDays('1899-12-30',int(item()?['Delete Date']),'yyyy-MM-dd'))

We got this from shanebart.com

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report
Copy link

Link copied!

Genius !!! Thx @phishseng91

Was this reply helpful? Yes No