As you may know, Excel stores the numbers internally in a number format and not a date. This topic was covered a while ago when I wrote how to convert a number to date to use it in Power Automate. Today we’re going to convert a date to an Excel number using Power Automate. This is a bit trickier, but as always, let’s take things to step by step to understand.
If you only want the formula, here it is:
add(div(sub(ticks(<INSERT HERE THE DATE TO CONVERT>),ticks('1900-01-01T00:00:00Z')),864000000000),1)
Otherwise, let’s go!
First, let’s understand what we’ll do. Excel stores a number being the number of days since the reference date, in our case being January 1st, 1900. So if we want to represent the date of this article, the 10th of August 2021, we’ll get the number 44417.
In Power Automate, we have a function that will convert a date into a reference, but this one will provide us a number. Still, this one returns the number of ticks (100 nanoseconds interval) since 1 January 0001 00:00:00 UT. It’s a huge number, and it doesn’t provide us with the same starting reference as before.
So here’s what we’re going to do:
- Find the difference in ticks since Excel’s start date and the date we want to calculate.
- Convert the nanoseconds into days
- Add one.
The last step is the one is that trips everyone. To understand that, we need to understand how ticks work. They count the number of days since the reference day. So the 2nd January 0001 is 1 since it’s one day elapsed. Excel does the same thing, but the 2nd January 1900 is the day (the next day after the reference) returns 2 since the first day is 1.
So with the ticks, the second day of the reference represents one day elapsed from the reference (1).
In Excel represents the second day of the reference (2).
We’re almost there! Now we need to convert ticks (nanoseconds) to days. To do that, here’s the formula:
From now on, the number “864000000000” will be referred to as the “scary number” 😀. We only need to divide the number we get from the ticks function to get the days.
MATH’s over! Now that we know how to calculate, here’s the Flow:
Only four steps.
- Get the date
- Set a variable with the scary number
- Do the math
- Respond with the number
Here’s the formula:
You can simplify it with:
If you know the date you want to convert, you can insert it if you keep the ISO format.
The formula is exactly like we described above
- Convert both dates to ticks
- Subtract them to get the difference between them
- Convert the difference to days by dividing it by the scary number.
Testing with today’s date (10th of August 2021) we’ll get the following:
I know the math looks scary, but the formula becomes simple once you understand the “gotcha” in the calculation. I probably went a bit overboard with the explanation, but I prefer to have all steps explained slowly so that you understand everything than to have something short that doesn’t make sense.