Power Automate: Convert Date to Excel Number

Bookmark

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 previous Project Manager, and Developer now focused on delivering quality articles and projects here on the site. I've worked in the past for companies like Bayer, Sybase (now SAP), and Pestana Hotel Group and using that knowledge to help you automate your daily tasks

View all posts by Manuel Gomes →

8 thoughts on “Power Automate: Convert Date to Excel Number

  1. 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

    1. 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

  2. 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?

    1. 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!

  3. 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)

Leave a Reply

Your email address will not be published.