Power Automate: Convert Date to Excel Number

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.

Warning:
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.

I use the date formats interchangeably, so you’ll see it represented in the US format or in the British format. This way I’ll try to have a representation of all locations.

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:

  1. Find the difference in ticks since Excel’s start date and the date we want to calculate.
  2. Convert the nanoseconds into days
  3. 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.

  1. Get the date
  2. Set a variable with the scary number
  3. Do the math
  4. 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

  1. Convert both dates to ticks
  2. Subtract them to get the difference between them
  3. 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.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Mick Haupt on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: