by 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.
There will be math! 😀. I’ll do all the math for you, but I just wanted to warn you!
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!
The calculation
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.
The Flow
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:
add(div(sub(ticks(triggerBody()['date']),ticks('1900-01-01T00:00:00Z')), outputs('Convert_ms_to_days')),1)
You can simplify it with:
add(div(sub(ticks(triggerBody()['date']),ticks('1900-01-01T00:00:00Z')),864000000000),1)
If you know the date you want to convert, you can insert it if you keep the ISO format.
add(div(sub(ticks('yyyy-MM-ddThh:mm:ssZ'),ticks('1900-01-01T00:00:00Z')),864000000000),1)
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:
Done!
Final thoughts
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.
Photo by Mick Haupt on Unsplash
Hello Manuel. I am trying to get from
2021-12-02T16:52:09Z
to
44532.4945486111
I have tried your great your formula above but have failed. Do you know any other way to convert a date to the long “up to seconds” integer.
Thank you
Hi Robin,
It’s a tricky topic, but I’ll work on it. Going up to the seconds needs more math, and the accuracy won’t be perfect.
If I don’t post something soon, please remind me because this is a super interesting and useful thing to have
Manuel
It doesn’t seem like it requires any more math. The way you have the function written, the output of both ticks() functions are integers so the sub() function also returns an integer. Then the way you input the 864000000000,it is treated as an integer so the div() function returns an integer. If you initialize a Float type variable and use it to calculate one of your tick() functions, or you input 864000000000.0, the div() function would then return a float type and you’d get a decimal number result instead of an integer so you’d get the time portion as well. I also had to add 2 instead of 1 to get everything to work correctly as already mentioned in a couple of other comments.
Despite I’ve also needed to add 2 instead of 1 I am grateful for this formula. ChatGPT failed here completely providing wrong numbers. This is what I got from it:
div(sub(ticks(), 621355968000000000), 10000000)
div(sub(ticks(), 25569) * 86400, 1) – notes: * is not allowed, replacing with mul() function causes overflow
div(sub(ticks(), 637166400000000000), 10000000)
Hi Manuel,
Great article I must say 🙂
But’ I still have a “small” query.
I have this Excel table with 300 lines of data collected on different dates and a column called ‘Date’ with those date values.
Now, every Sunday morning I need a flow to run and manipulate the rows with dates of the past week only.
So, I figured I’d first run a ‘Compose’ to subtract 7 days from ‘utcNow()’ and then manipulate that date as you’ve suggested in this article to receive the serial number of that date.
Then I’d run the ‘List rows present in a table’ action with an oData query like this: Data ge ‘outputs(‘Compose’)’
But I get this nasty error message:
Invalid filter clause: unsupported operation. Only single ‘eq’, ‘ne’, ‘contains’, ‘startswith’ or ‘endswith’ is currently supported.
clientRequestId: 2df83fc2-f275-4ab8-a7f0-d2cbee520ab0
serviceRequestId: b6474abe-ddfd-4782-ab94-7d8c7fe10e4c;a4a48858-e523-4e3b-9bb9-9e3bcd1c013a;3d4791b3-a5ac-4bba-b2e7-2f892de5fb29
I’m puzzled 🙁
Have any suggestion?
Hi,
It’s unfortunate, but “ge” is not supported.
But can I recommend a workaround? If you can have an extra column in Excel with that calculation of data that displays if the value is to be parsed or not. Something like “Yes” or “No” for example. Then you only need to do “eq” ‘Yes’ and you’ll get all the values.
Would this be possible?
Thanks!
The genius of simplicity !!
Great idea.
Gave it a try and ‘Voila’
Works like a charm 🙂
Thanx pal 👍
Awesome!!! :).
Well dome!
I think your formula is a bit off. For date 08/10/2021 the excel number value is 44418.00
I got the same result
Excel also says that today (29/07/22) is 44473 but your line returned 44472.
Not criticising, I’m sure there’s a good reason
Thank you for this.
I had the problem that excel switched the month and date if the date was single digit – So this sorted it out.
There is one thing I don’t understand though.
I had to add 2 at the end instead of 1. 1 was giving me the day before.
Example:
add(div(sub(ticks(variables(items(‘Apply_to_each’)?[‘Usage Start Date’])),ticks(‘1900-01-01T00:00:00Z’)),864000000000),2)
I found the problem of the additional day required (2 in total).
In Power Automate formula above:
If you use ‘1900-02-28T00:00:00Z’ Excel displays 28/02/1900 -> OK
If you use ‘1900-03-01T00:00:00Z’ Excel displays 29/02/1900 -> This is wrong. That day of that year did not exist !
Actually, if you try to use ‘1900-02-29T00:00:00Z’ in Power Automate formula, you will get an error.
So we need to add 1 additional day to compensate the excel problem (2 in total).