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.
You can download it from the cookbook section or below.
Convert Excel number into a Date up to the second
This template will enable you to convert an Excel number to a date, including the seconds. Please note that you can use this template as a “function” where you can call it from anywhere, which will return the information you want. Please note that I’m producing a String in UTC format, so it’s easier to convert it than to your timezone.
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:
- 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.
- 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.
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.
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
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):
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:
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.
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