Answered

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

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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"

"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

Categories:

Using connectors

I have the same question (0)

All responses (11)

Answers (1)

Sort by

Verified answer

Pstork1 69,599 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(3)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(3)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ericb27 13 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Pstork1 69,599 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Pstork1 69,599 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No
Pstork1 69,599 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No
aurena 14 on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Pstork1 69,599 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
GUOGUO 389 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No