Unanswered

ISO 8601 conversion of Time or DateTime Excel dates does not work

(0) Share

Report

Posted on by D_DAL

Hi,

We have a regression with one of our flows. After searching, we have found that the ISO 8601 conversion of time or datetime Excel work no more.

To be sure, I have created today a sample :

The flow reads the table.
Here the output:

Only the date without a time value is converted toward ISO 8601 format.
The time and datetime are no more converted.

It was working before, for example:

Any hints to solve this failure?

Thank you.

Categories:

General topics

I have the same question (0)

All responses (2)

Answers (0)

AWorley 414 Super User 2024 Season 1 on at

Like (0)

Report

Hello, The best way to handle date time in power automate is to convert the date time into "ticks". This will allow you to convert the date time into an integer to run conditional statements off of.

For example here is the expression I use to validate if the current time of running is within 6:00 AM and 12:00 PM Eastern Time.

if(and(greater(ticks(addHours(utcNow(), -4)), ticks(concat(formatDateTime(addHours(utcNow(), -4), 'yyyy-MM-dd'), 'T05:50:00Z'))), less(ticks(addHours(utcNow(), -5)), ticks(concat(formatDateTime(addHours(utcNow(), -5), 'yyyy-MM-dd'), 'T12:01:00Z')))), true, false)

For excel the excel specifics

How excel reads dates and times is by an numerical integer. If you were to have a date time value then convert it to a number data type in excel you would get a number. For example here's the numerical value for 12/1/2023 at 1:00 AM in excel.

For your Time field excel would render that value as shown below:

The best way to handle date times in Power Automate is to ingest the excel value as a string, assign it to a variable or a Convert Date Time Action, then work with the value exclusively in PA rather than Excel.

Was this reply helpful? Yes No
D_DAL 2 on at

Like (0)

Report

Hi AWorley,

Thank you for your help.

First, we had to implement a formula to publish in Excel dates Excel can handle properly (thanks to https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Excel-Date-changes-in-Power-Automate/ba-p/799959).

Now, we are facing a regression because the ISO 8601 conversion by Power Automate is no more able to handle properly datetimes. So, we have to implement a workaround in our Excel tables to duplicate each date, time, datetime column as a string column with a formula to produce directly the correct ISO 8601 value.
And adapt all our flows to use these duplicate columns to work with.

Forgive my French, the qualifier “Power” is totally usurped when it comes to Excel.

Was this reply helpful? Yes No