Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to get date from Excel as a date in Power Automate flow

Posted on October 30, 2022October 30, 2022 by Tom

“I have an Excel file with dates in one column, but Power Automate returns a number instead of the date, how can I fix it?”


Unlike most data sources, Excel stores dates in a specific way. It’s not in the ISO format year-month-date, but a sequence number. And that’s also what the ‘List rows present in a table’ action returns – a number. If you store only date, without time, it’ll return a whole number; if you include also time you’ll get a decimal number.

It’s correct, but it’s not the value you need (unless you’re building a reminder). How do you convert it into a date to work with it as a date?

Switch the action to ISO 8601 format

The easiest approach is to switch the date directly in the ‘List rows…’ action. Click on the ‘Show advanced options’ and switch the DateTime Format to ‘ISO 8601’.

Power Automate Excel get date

Convert the number into a date

The other, more complicated option is to convert the number into a date. It’s an alternate approach in case the ISO conversion doesn’t work (as happened in one of my flows).

The number as provided by Excel is a number of days since the 30th of December 1899. To convert it into a date just add the days to the date. Although it’s a bit different depending on the date format – if it’s a date only or date and time.

Convert date only column into date

It’s much easier for a date only column as it provides a whole number. Take the number and add it as days to the “base date”, e.g.

addDays('12/30/1899',int(items('Apply_to_each')?['Počáteční čas']),'yyyy-MM-dd')

Convert date and time column into date

If the column contains date and time it’ll return a decimal number. That’s a problem as you can add only full number of days. Therefore, if you need only the date, without time, you must remove the decimal part of the number before the calculation. Split(…) it by the dot and keep only the first part, e.g.

addDays('12/30/1899',int(split(items('Apply_to_each')?['Počáteční čas'],'.')[0]),'yyyy-MM-dd')

But if you want also the time, you’ll need a few more calculations to extract it from the decimal number.

Firstly, extract the decimal part from the number…

split(items('Apply_to_each')?['Počáteční čas'],'.')[1]

…add leading zero and convert it into a decimal number using the float(…) expression…

float(concat('0.', split(items('Apply_to_each')?['Počáteční čas'],'.')[1]))

…and divide it by 1/24, the decimal representation of 1 hour. You must use the float(…) expression in the division to get a decimal result.

div(float(concat('0.', split(items('Apply_to_each')?['Počáteční čas'],'.')[1])),div(1,float(24)))

The result will be another decimal number – a number of hours e.g. 13.8208333344.

Power Automate Excel get date

Since it’s another decimal number, repeat the process to extract also minutes from the hours number…

div(float(concat('0.', split(string(outputs('Compose_2')),'.')[1])),div(1,float(60)))

…and then seconds from the minutes number.

div(float(concat('0.', split(string(outputs('Compose_3')),'.')[1])),div(1,float(60)))
Power Automate Excel get date

Put it all together and build the date including the time. As the formatNumber(…) expression would round the results, convert them all into string(…) before splitting them by the dot and taking only the first part.

Concat(outputs('Compose'),'T',split(string(outputs('Compose_2')),'.')[0],':',split(string(outputs('Compose_3')),'.')[0],':',split(string(outputs('Compose_4')),'.')[0])
Power Automate Excel get date

Summary

To get a date from Excel in your Power Automate flow you’ve got two options. The easy one is to configure it directly in the action, and if it works, you’re done. The second one is to calculate it from the number – get the date by adding days, and then use an extensive calculation to get also the time if needed.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes