Unanswered

Flow datetime error

(0) Share

Report

Posted on by eis5187

I had a flow that was running just fine for about a month; it looks at rows in an Excel table and, if the date condition is met, it sends an email. The DateTime Format is set to ISO 8601.

All of a sudden today I received the error: Unable to process template language expressions for action 'Condition' at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '45078' was not valid. The datetime string must match ISO 8601 format.'..

No one has changed the date format in the Excel file. So I'm confused as to why I'm having this problem all of a sudden.

Ideas? Thanks!

Categories:

Using flows

I have the same question (0)

All responses (11)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Do you have any blanks in that date column from excel? Whenever there is a blank it throws off how power automate reads the value.

https://powerusers.microsoft.com/t5/Building-Flows/When-Excel-first-row-has-blank-date-all-remaining-row-dates-will/m-p/1625275

Was this reply helpful? Yes No
eis5187 14 on at

Like (0)

Report
No, no blank rows in the data. I can see from the outputs it thinks the Event Date column is coming in as
"Event Date":"45078"
But, I changed it in Excel so it looks like this:

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

The value "Event Date":"45078" is the date in a serial format. Excel only uses serial numbers for dates.

Did changing the date format of excel to "2023-06-01T00:00:00Z" fix your issue?

Was this reply helpful? Yes No
eis5187 14 on at

Like (0)

Report

No, it did not.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

I would go check the Excel sheet where the date is "45078" which should be May 19, 2023. Check the date itself as well as the row above it and see if there is any reason it looks different. Like maybe it was put in as text or has an additional space.
Next I would clean the dates by adding a new column and getting the dates into it by adding 0, then copy and paste over itself. I do that so that I'm not copying anything like extra formats or something. Then I would retry the flow using the new column.
See if you still get the same error when using the new column.

It seems like your flow is working, it only stops working when the Apply to Each reaches that date? Or do all of the Apply to Each fail?

Was this reply helpful? Yes No
eis5187 14 on at

Like (1)

Report

Hmm, I don't have any May 19th options. Closest thing would be June 1st. There's no way for the dates to be different because a Form is populating that field.

I did that and got the same error using the new column.

The rest of the flow is working.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

You are correct. The serial date "45078" is June 1, 2023. I just check in Excel. Previously I used some internet time converter and I must have looked at it incorrectly.

So at least we know the issue seems to be at the first date row where it is 6/1/2023.

Can you take another screenshot that let's me see the List rows present in the table details. You need to expand the entire step so I can see all of the options down to the Date Time format. Also, I want to see the full file name, so don't blank or scratch it out.

Was this reply helpful? Yes No
eis5187 14 on at

Like (1)

Report

The first expression is:
formatDateTime(items('Apply_to_each')?['Event Date'],'MM/dd/yyyy')

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Ok, I have my flow set up exactly like yours and I am not getting that error.

There is something going on within the excel file itself as opposed to something incorrect in the flow. Either the excel fields are in some kind of format like text or there is something in there that is making it not appear as a date field to Power Automate when it pulls in the Excel values.
I don't know what it could be, you will have to look at the formats and see if there is something different or how it is getting put into the cell in the first place. Like are there "" quotes around it or something?

It could be something with the line breaks. I notice in one example of your excel there is a big gap above the date, and then in another example there is no gap. Why are the row heights different? Is it due to spaces in the date fields? How do you have some dates at the top of the cell and then some dates at the bottom??

A good way to test this will be to put a Compose step above the condition, and then insert the Event Date field in there without any formatting. So you can see how it looks to power automate. You would expect the Compose to show the full date time value in IS 8601 format. However, if it is just showing the serial you know it is getting pulled in to PA incorrectly. Also you'll be able to see if it is green or black. If it is green that means it is being read as a number. It should be black like a string.

So First Thing:
Put a Compose step above the condition with the Event Date and run the flow and see what it looks like.
Is the result in a IS 8601 format? What color is it?

Second Thing:
You need to correct that condition step, because you will never get a comparable answer. You are trying to compare two different formats of date. You have the Event Date as 'MM/dd/yyyy' and the Yesterday as 'yyyy-MM-dd' and those will never be equal. Both date time formats should be 'MM/dd/yyyy' or 'yyyy-MM-dd'.

Try the compose step and let me know what it looks like.

Was this reply helpful? Yes No
eis5187 14 on at

Like (0)

Report

Sorry, the date time formats have been the same when I run it. I'd been testing different formats and the mismatch was an oversight of the example I sent you. Here are the results:

Was this reply helpful? Yes No