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

[Anonymous]

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

[Pstork1 · Accepted solution]

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.

[Anonymous]

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]

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]

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.

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

[Pstork1]

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.

[Pstork1]

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)

[Pstork1]

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)

[aurena]

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]

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.

[GUOGUO]

Hi Pstok,

Could you please help on the following error message? Thanks in advance .

I tried with two expression in Compose.

1

div(add(div(float(ticks('LIB Global Item Status')),10000000),-59926435200),86400)

2formatDateTime(addDays('1899-12-30', int(items('Apply_to_each')?['LIB Global Item Status'])),'MM/dd/yyyy')

but both of them doesn't with the same error message. Thanks.

[Pstork1]

Please post as a new question in the forum instead of tagging on to an existing thread.  You'll get more help that way and people with the same problem will be able to find an answer later.