Getting Excel date and time in Power Automate

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.

Lets start

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

3802784400

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