cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MG4
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
Pstork1
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
Pstork1
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.

SSGGSSGG
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.

ericb27
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

Pstork1
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.
Pstork1
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.
Pstork1
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.
aurena
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. 

Pstork1
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
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 (59,651)