Skip to main content

Notifications

Community site session details

Community site session details

Session Id : nqNvX5qIOqxwf4985rM6Ko
Power Automate - General Discussion
Unanswered

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

Like (0) ShareShare
ReportReport
Posted on 26 Oct 2023 14:09:08 by 2

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 :

D_DAL_0-1698328521526.pngD_DAL_1-1698328564125.png

D_DAL_2-1698328611505.png

The flow reads the table.
Here the output:

D_DAL_3-1698328754017.png

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:

D_DAL_4-1698329238599.png

 


Any hints to solve this failure?

Thank you.

  • D_DAL Profile Picture
    2 on 27 Oct 2023 at 09:19:43
    Re: ISO 8601 conversion of Time or DateTime Excel dates does not work

    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.

    D_DAL_0-1698397948347.png

    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.

  • AWorley Profile Picture
    414 Super User 2024 Season 1 on 26 Oct 2023 at 14:29:18
    Re: ISO 8601 conversion of Time or DateTime Excel dates does not work

    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. 

     

    AWorley_1-1698329735678.png

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

    AWorley_0-1698330476403.png

     

     

    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. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,089 Most Valuable Professional

Leaderboard
Loading started