change in the way power automate reads excel dates

(0) Share

Report

Posted on by natanz

i have a flow that until today worked fine when it used the formula

addDays('1899-12-30',int(items('Apply_to_each')?['ConstructionStart']),'MM.dd.yyyy')

today i got errors on it, telling me that int wasn't able to resolve (items('Apply_to_each')?['ConstructionStart']) to an integer.

apply_to_each is reading data from an excel spreadsheet, and ConstructionStart is a column in my table formatted as a date.

I found when i looked at previously successful runs, that the data incoming from the get excel list looked like this when i ran it last week

"ConstructionStart":"45754"

but looks like this when i ran it today

"ConstructionStart":"2018-09-11T00:00:00.000Z"

I guess this is Microsoft living it's best life, switching things up, to give us all some better bugs to squash.

two possible solutions which i am trying.

1) change the number format of the column in my excel table to "general" suboptimal, but very likely to work

2) change the formula to remove the int function addDays('1899-12-30',items('Apply_to_each')?['ConstructionStart'],'MM.dd.yyyy')

thoughts? similar experiences? something else?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

Pstork1 68,717 Most Valuable Professional on at

Like (1)

Report

I'm currently working on a blog post to explain the change and provide some workarounds. What are you trying to do with the Construction Start field? You don't need to use the base date of 1899-12-30 anymore since the date is being reported in ISO 8601 format. But you may still need to process it depending on what you are trying to do with it in the flow

Was this reply helpful? Yes No
natanz 17 on at

Like (0)

Report

i will be interested to read your blog post. All i need to do is move a date field from excel to SP. I used the whole add days formula because of the way power automate used to read dates from excel. If it's an ISO8601 format, can i just drop the date in directly, and both excel and SPO will recognize that it is a date?

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Yes, you can use the ISO 8601 format directly. That's the advantage to the change. The one issue you may have is that the Excel datetime is time zone independent and the ISO 8601 format assumes UTC. So when you add it directly to SPO it can cause issues since SPO will assume local time zone. Here's the BLOG article.

Excel Date changes in Power Automate – What Me Pa..Panic? (dontpapanic.com)

Was this reply helpful? Yes No
tpmeredith 41 on at

Like (0)

Report

Mine went to the ISO format (breaking a flow I had due to the prior workaround), I fixed it.. now a day later the same flow switched "back". Ugh a headache!

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (1)

Report

I heard back from MS today that the ISO format was a regression bug. They decided to roll it back which reverted it to the original format. I did complain that this would just mean more headaches for the people who already had to fix flows. But they rolled it back anyway.

Was this reply helpful? Yes No
marymascari 18 on at

Like (1)

Report

Do they publish this information anywhere?

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Normally, these kinds of things can be found here:

Product Roadmap | Power Automate (microsoft.com)

or on the Power platform blog here:

Power Automate - Blog (microsoft.com)

In this case it appears it was a regression bug that wasn't expected and therefore it wasn't on the roadmap.

Was this reply helpful? Yes No
tpmeredith 41 on at

Like (0)

Report

This is probably my single most complaint with Flow/Power Automate. We've used it extensively for over 2 years and it seems like quarterly that MS is changing core connectors behaviors with no warning or seemingly no testing. Why do some connectors utilize "Preview connectors" and some connectors they just randomly push updates to? Any changes to connectors like this should be made via "Preview Connector" first and have extensive testing before potentially wrecking everyone's flows. Further changes need to be communicated, especially if they randomly roll it back a week later.

Aggravating.

Was this reply helpful? Yes No