Power Automate: Convert Excel Number to Date (to the second)

I already wrote about how to “Convert Excel Number to Date” in the past, but I got a good question. How about the minutes and seconds? I’m only converting the date, but what about the time? Excel provides us with the decimal part that we’re ignoring.

Today we fixed that with a new template that solves converts the date and time.

The template

You can download it from the cookbook section or below.

The template is a bit more complex since we need to do a bunch of divisions to get it to work, so you can import it and ignore the math or read forward to understand what’s happening.

One warning. Power Automate was not built for extreme precision, so that some seconds may be “off.” Please consider this and don’t use it where a second can break your application. This is because of two main things:

1. Since we only have the remainder of the number, we must keep dividing it to get the hours, minutes, and seconds. We’re using the results to feed the following calculation, but precision errors are still propagating from calculation to computation. There’s not a lot that we can do.
2. We could minimize this by rounding, but the issue is that Power Automate doesn’t have a round function. So if we cannot round it properly, we need to propagate the information.

Caveats aside, let’s see what’s happening.

Parallel calculation

I put the calculation parallel to get a bit more performance and for the Flow to run faster and separate the calculation of both parts. It’s easier to analyze that way.

If you’re interested in the date part, please check the article that explains it in detail.

Here’s the overview of what’s happening

We have three main sections. The constants where we’ll define the values for the calculation. The calculation itself and add the values to the date we calculated.

Constants

As we’ve seen before, the constants are essentially divisions of the parts per day that each part of the time; the number represents a fraction of the total 24h.

So let’s try to simplify this. If we have 0,5, we can “cut” 2 clean slices and provide the user with 12:00:00. But how about 0,66. We can’t cut it into neat pieces, but we can try to cut as many as possible; the rest represent less than an hour. The values of less than one h are the minutes we can calculate similarly.

For the constants, we have:

1/24 = 0,041666667 <- 1 hour
1/60 = 0,016666667 <- 1 minute or 1 second

Let’s try to calculate the values

The calculation

So here’s how to calculate.

I’ll flag each of these steps below so you can follow them.

First, divide by the number of hours a day (1/24), and we get a value. If we get the integer part, we get the hour and the decimal the minutes. Then we get the minutes and do the same by dividing the number of minutes by an hour (1/60). We get the integer part, the number of minutes possible, and the remainder of the seconds. We do it one more time and get the seconds.

Let’s look at an example:

19/07/2022 16:12:13 

Excel number will be:

44761,675150463000

We remove the decimal part, and we get (step 1):

0.675150463000

Let’s see how many hours we can get from this. To do that, let’s divide it by the value above for 1h (Step 2):

0.675150463000 / 0.041666667 = 16.20361111

We have 16 hours and some more stuff (minutes). Let’s remove the decimal part (Step 3) and do the same but with the minutes constant (Step 4):

0.20361111 / 0.016666667 = 12.21666667

We have 12 minutes and more stuff (seconds). One last time remove the decimal part (Step 5) and divide (Step 6):

0.21666667 / 0.016666667 = 13,00000015

We get 13 whole seconds.

Putting everything together will represent 16:12:13.

Adding the values to the date.

Now that we have hours, minutes, and seconds we can add them to the date we calculated. To do that, we have three functions that can help us:

1. addHours
2. addMinutes
3. addSeconds

We only need to remove the decimal part of each value before and add them using the function.

After doing this, we only need to return the date, and we’re finished.

Final thoughts

There’s a lot of strange math, but I hope my explanation helped you understand a bit better what’s happening. But notice that you don’t need to understand the math to make it work for you. You can add this to your project and use it as a “black box” by using the “Run Child Flow” action, for example.

Photo by Kai Gradert on Unsplash