cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
natanz
Frequent Visitor

change in the way power automate reads excel dates

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?

8 REPLIES 8
Pstork1
Dual Super User III
Dual Super User III

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



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
natanz
Frequent Visitor

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?

Pstork1
Dual Super User III
Dual Super User III

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)



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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!

Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
marymascari
Advocate I
Advocate I

Do they publish this information anywhere?

Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
timothym
Advocate I
Advocate I

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.

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (6,503)