Date and T00:00:00.000Z

[Anthony_]

Hello,

My flows heavily rely on dates, so this is a big problem for me.

Last week my dates started coming through Power Automate in a different format.

Example

Now: "Due Date": "2021-01-21T00:00:00.000Z"

Before: "Due Date": "44217"

I do not know why they are pulling a timestamp through now.

Microsoft support is trying but unsure as well, they have been unable to replicate the problem.

I am open to thoughts and ideas.

I use excel, PA and power queries within my excel sheets.

Thanks,

Anthony

[Pstork1]

I'm writing a blog post on this change right now.  Sometime around January 15, 2021 a change was made to the List Rows action in the Excel connector that stopped bringing dates in as Serial dates (where number was the number of days and fractional days - seconds since 12/30/1899).  The newer format is to bring those timedate fields in as an ISO 8601 formatted string in the UTC time zone.  Date only fields are brought in with the time set to midnight and Time only fields are brought in with the date set to 12/30/1899.  The good news is that this means you don't have to use a long formula to get the datetime field into a date format.  The bad news is that you no longer get a number that is easy to use in calculations.

I'll post a link to my blog with a full explanation as soon as I finish and publish it.

[Anthony_]

Hi Pstork1,

Thanks for the information.

This explains why my get a row still comes through normally.

I am having trouble creating new expressions because of different formats.

Even when I use split to remove the T...Z and covert it to the proper number, my filtering does not work..

I used to use this:

if(empty(body('Get_a_row')?['Yesterday']), null, addDays('1899-12-30', int(body('Get_a_row')?['Yesterday']), 'yyyy-MM-dd'))

Do you have any thoughts?

[Pstork1]

You should be able to sort using the value as it comes through now without removing the T and Z. so the formula you quoted would now be.

if(empty(body('Get_a_row')?['Yesterday']), null, body('Get_a_row')?['Yesterday'])

[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)

[marymascari]

Was MSFT going to tell anyone about this? 😠

Or if they did communicate this change, where was it, so I can check there frequently and not learn about this sort of thing when my flows break?

[Pstork1]

Normally this kind of thing should have been in the release notes on the road map.  I haven't been able to find a reference to it and have raised that specific question with the product team.  I think in this case it just slipped through the cracks.