Date and T00:00:00.000Z

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Using flows

I have the same question (0)

All responses (18)

Answers (0)

Pstork1 69,129 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

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

Like (0)

Report
Copy link

Link copied!

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?

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

Like (0)

Report
Copy link

Link copied!
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'])

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

Like (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
marymascari 18 on at

Like (0)

Report
Copy link

Link copied!

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?

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

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Cherif 4 on at

Like (0)

Report
Copy link

Link copied!

Hi Pstork1, I am having a similar issue, but I am not sure if it's the exact issue or not. I am building a pivot table based on a query data, the format for my "Entry Date" is set to be Date in the query, however, in the pivot table when I try to refer to it, it shows as [2018-12-31TT00:00:00]. is there a way to just keep the date and remove the rest?

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

Like (0)

Report
Copy link

Link copied!

I don't have an answer for that since I haven't done much with pivot tables. You'll get more responses if you post this question as a new question in the forums rather than tagging on to an older thread.

Was this reply helpful? Yes No
Cherif 4 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your quick response

Was this reply helpful? Yes No
Moto-moto 65 on at

Like (0)

Report
Copy link

Link copied!

Did you manage to fix this because i am having the same issue

Was this reply helpful? Yes No