Working with Excel date and time in Power Automate some times get tricky. The reason for that is Excel doesn’t return the date and time as it is instead it returns in form of integers or decimal numbers.
In my previous blog, i explained how to get the Date from Excel. Today we will talk about what if you excel field has both date and time.
So lets look at our test excel.
As you can see i have one column of type Date and Time and i have formatted the column.
Now when i am using Power Automate i get a decimal number
So how would i get this number back to a Date and Time in Excel.
So we know that Excel counts everything from 1-1-1900.
And we also know that there are 24 hours in a day and 60 minutes in one hour and 60 seconds in a minute so that will make
60x60x24 = 86400 Seconds
Lets multiply our Decimal number with this total number of seconds. This will give us total number of seconds since 1-1-1900
44013.7083333333×86400 = 3,802,784,399.999997
Lets get rid of the decimals and round it off
Now simply add these total number of seconds to our legendary date of 1899-12-30 and you will get the actual formula.
So to sum it up, here is the formula for Power Automate
addseconds('1899-12-30', int(formatNumber(mul(float(body('Get_a_row')?['Time']),86400),'0','en-us')),'yyyy-MM-dd HH:mm:ss')
body(‘Get_a_row’)?[‘Time’]) is my variable from excel