cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Excel: List Rows in a Table: Date behavior change?

Hi Power Automate Community,

 

Has there been a recent change in behavior for List Rows in a Table Excel connector? Specifically as it relates to date formats?

 

Starting on 1/14 the output of that call changed from 

"DATE2":"44047"

to

"DATE2":"2020-07-27T00:00:00.000Z"

even though the column format in excel file had not been touched.
In fact the column in question is hidden and protected in file. And the flow runs through the same steps for multiple files, all showing the same change in behavior.

Up until 1/13 the flow had been running daily without issues for months.

 

Pretty stumped

Thanks
Manuel

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User III
Dual Super User III

Yes, there has been a change.  Previously Time/Date columns in Excel were brought in as they are stored in Excel,  They were a double number where the integer portion represented the number of days since 12/30/1899 and the decimal represented the fractional number of days as seconds.  The first thing everyone had to do was do math to translate the number to an actual date.  The connector was recently updated to do that for you.  The format ending in Z is the Date and Time in ISO 8601 format for UTC time zone.



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

View solution in original post

9 REPLIES 9
Dual Super User III
Dual Super User III

Yes, there has been a change.  Previously Time/Date columns in Excel were brought in as they are stored in Excel,  They were a double number where the integer portion represented the number of days since 12/30/1899 and the decimal represented the fractional number of days as seconds.  The first thing everyone had to do was do math to translate the number to an actual date.  The connector was recently updated to do that for you.  The format ending in Z is the Date and Time in ISO 8601 format for UTC time zone.



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

View solution in original post

Thanks.

 

Is there a feed somewhere that indicates these types behavioral changes? That was one of the first things I searched for but couldn't find any indication.

Seems like something that ideally you would want to know about ahead of time.

Regular Visitor

This change, completely unannounced, broke a dozen flow I have to maintain. There is zero backwards compatibility, I have to manually edit all the flows instead of providing us developers with a way to keep backwards compatibility. This is insane, every developer who maintains flow connectors to Excel and needs date/time data has already worked to handle float values and their code can't suddenly handle strings.

Regular Visitor

This change appears to have broken several of our flows where the column has date fields as the first row and then integers below. Take this example below in excel flow is now treating all values as a date. The "Updated At" row value is a date field. The values below the date are a general field I even tried changing them to text instead, no dice.

 

Screen Shot 2021-01-19 at 14.44.27.png

 

Here is what flow returns as the value. Used to be a text value now its a date.

 

Screen Shot 2021-01-19 at 14.46.38.png

Dual Super User III
Dual Super User III

I have not seen any notice of this change either.  I'm still looking.  In the meantime I'm putting together a blog post to explain it and provide some workarounds.



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

I just finished posting a blog article explaining the change and providing some suggestions on how to work around it.  You can find the blog post here:

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.
Dual Super User III
Dual Super User III

I just finished posting a blog article explaining the change and providing some suggestions on how to work around it.  You can find the blog post here:

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.
Frequent Visitor

It looks like it reverted back to the integer format again per my last test (1/21/2021). I was working on switching my flows when they started functioning again. To be honest, I wasn't even sure how to work with the normal date time stamp format when I was trying to filter so I'm glad it continues staying as is for now. 

Dual Super User III
Dual Super User III

Yes, I just got a message back that the original change was a regression bug and that they were going to roll it back today.



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

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (68,201)