“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.