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.

[Cherif]

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?

[Pstork1]

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.

[Cherif]

Thanks for your quick response

[Moto-moto]

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

[Anthony_1]

I fixed this by manipulating data in additional columns within my sheet as it flowed better for me

[Moto-moto]

it will be much appreciated if you can show how you did it because don't know what to do please and thank you.

[Anthony_1]

Hi Moto-moto,

In this case I used the formula =TEXT([@Today], "yyyy-MM-dd") to convert the date into a string. I found it easier to work with because it was my end goal anyway.

I used to convert 44690 into 2022-05-09 with an expression but could not do it after the change. There are probably a few ways around it this was just easiest for me.

PA Today column - after change - 2021-05-09T00:00:00.000Z

[Moto-moto]

Thanks Buddy

[frdaus]

Hi bro. My issue is very similar to yours. Could you please share the formula you used in Excel to remove the T00:00:00.000Z? Because when I used =TEXT([@Today], "yyyy-MM-dd") in Power apps, the data still returned with the T00:00:00.000Z in Excel

[Anthony_1]

Hi frdaus,

Slightly different scenario, I am converting a date (yyyy-MM-dd) into a string in excel for power automate to read.

It sounds like your data is in power apps and it is already T00:00:00.000Z.

I think you want it converted in excel so here is an example.

Cell M841

2022-05-09T11:05:53Z

to

2022-05-09

with the formula: =LEFT(M841, SEARCH("T", M841, 1)-1)

Hope this helps.

[DiegoZubu1]

Hello,
My Power Automate has the same problem. Every time i post something the date and time change... Anyone can help me?

[Will_23]

I found a solution on this that worked for me.

If you go into the related table in Power Apps, find the date column you need to fix and right-click, Edit Column. Then go to Advanced options and there should be a field call Time zone adjustment. If you change this to Date only that should fix it.

Hope that helps.