cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Date and T00:00:00.000Z

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

 
6 REPLIES 6
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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?

 

 

Dual Super User III
Dual Super User III

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'])


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Dual Super User III
Dual Super User III

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)



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Advocate I
Advocate I

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?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (34,793)