“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’.
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.
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)))
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])
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.
Your manual conversion of the datetime *almost* works, but fails when the the hour, minute, or second is a single digit, resulting in something like “2022-10-21T13:1:5” instead of “2022-10-21T13:01:05”.
Here’s an even more convoluted version of Compose 5 to fix that:
concat(outputs(‘Compose’),’T’, if(equals(length(split(string(outputs(‘Compose_2′)),’.’)[0]),1), concat(‘0’,split(string(outputs(‘Compose_2′)),’.’)[0]), split(string(outputs(‘Compose_2′)),’.’)[0]),’:’, if(equals(length(split(string(outputs(‘Compose_3′)),’.’)[0]),1), concat(‘0’,split(string(outputs(‘Compose_3′)),’.’)[0] ), split(string(outputs(‘Compose_3′)),’.’)[0]),’:’, if(equals(length(split(string(outputs(‘Compose_4′)),’.’)[0]),1), concat(‘0’, split(string(outputs(‘Compose_4′)),’.’)[0]), split(string(outputs(‘Compose_4′)),’.’)[0]))
I encountered another issue.
If the seconds are exactly 0, there won’t be a ‘.’ to split on in Compose 4, so you’ll get an error.
Modified version of Compose 4:
div(float(concat(‘0.’, split( if(contains(string(outputs(‘Compose_3′)),’.’), string(outputs(‘Compose_3’)), concat(string(outputs(‘Compose_3′)),’.0′)),’.’)[1])),div(1,float(60)))
Same fix for Compose 3, just in case:
div(float(concat(‘0.’, split( if(contains(string(outputs(‘Compose_2′)),’.’), string(outputs(‘Compose_2’)), concat(string(outputs(‘Compose_2′)),’.0′)),’.’)[1])),div(1,float(60)))
Hello Ryan,
thank you for noticing and sharing the fix. These expressions are so complex that maybe spending a few cents on ChatGPT might be a better solution as shown a newer post: https://tomriha.com/how-to-use-chatgpt-in-your-power-automate-flow/
Hi Tom,
I came across this and wanted to share – MS simplified it (thankfully!). thank you so much for all of your help – you have been an invaluable asset to my work! thank you again!
forgot the link:https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Excel-Date-changes-in-Power-Automate/ba-p/799959
and the solution:
Coalesce(formatdatetime(Exceldate,”MM-DD-YYYY”), ” “)
I’ve just needed this information, that I can set the ISO format during action “List rows present in a table” @Tom, once again thanks !
When your source is anything but utc format, you can’t use the ISO8601 option. I imagine it will fail for anything like 24/12 and read dates like 10 February as 2 October.