Answered

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

(1) Share

Report

Posted on by Community Power Pla...

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

All responses (11)

Answers (1)

Pstork1 67,060 Most Valuable Professional on at

Like (0)

Report

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

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.
GUOGUO 389 on at

Like (0)

Report

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

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 67,060 Most Valuable Professional on at

Like (0)

Report

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

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.
aurena 14 on at

Like (1)

Report

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

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 67,060 Most Valuable Professional on at

Like (0)

Report

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

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 67,060 Most Valuable Professional on at

Like (1)

Report

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

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 67,060 Most Valuable Professional on at

Like (0)

Report

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

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.
ericb27 13 on at

Like (0)

Report

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

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.
Community Power Pla... on at

Like (3)

Report

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

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.
Community Power Pla... on at

Like (3)

Report

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

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.