November 17, 2024
Expressions and Templates / Microsoft / Power Automate

Power Automate: Convert Excel Number to Date

Manuel Gomesby Manuel Gomes14

When you’re fetching data from Excel, you would expect that Power Automate receives one from Excel. Both have the concept of dates, but something “strange” happens when you fetch it. Excel returns a number. What gives? You need to convert the Excel number to date so that you can work with it in Power Automate.

Why?

Excel stores internally the dates in a number format. It’s not a timestamp as in Linux (number of seconds since 1st of January 1970), but it’s something similar. In Excel’s case is the number of days since 1st January 1900. You can test this quickly by doing the following:

Add the number one in two cells:

Leave it as a number in one and change the other to date. There you’ll see the first date.

I’m sure Microsoft has a good reason to consider this date and to do things this way, but we’re interested in converting this number into a valid date that we can work within your Power Automate. So let’s do a template that we can re-use in all our other Flows.

The Power Automate

This Power Automate has four parts.

  1. Get the number
  2. Start a variable with the reference date (1st January 1900)
  3. Add the days to that date
  4. Return the date

Here’s the full Power Automate to Convert excel number to date. You can download it from my template archive right here. If you don’t know how to do it, I have you covered. Here’s a step by step article on how to do it.

It’s quite simple, but let’s break it down.

The input

I like using this kind of input since the introduction of “Run Child Flow” (only available on Solutions). You can easily replace it with an “HTTP Request,” and the result is the same.

The reference date

As mentioned before, the reference date is the 1st of January 1900. So we create it using the following formula:

formatDateTime('1-1-1900','dd-MM-yyyy')

Please note that we’re not considering the time. It’s because the definition is that it’s the number of days elapsed since the reference date, so we use the default time noon as a return value. You can ignore it altogether when parsing the date to your format.

Getting the current date

To get the current date, we need to add the number of days to the reference date. It’s a simple formula:

addDays(outputs('Compose'),sub(triggerBody()['number'],1))

Now I get the questions that I always get. Why sub the actual number by 1? If you simply add the number in the body with the date formatted, you’ll get tomorrow’s date and not today. The reference date starts at one and not at 0. I know it looks strange, but let me give you an example. Think as the reference day like today, so day one and, for the sake of argument, let’s say it’s a Sunday. If you think “2 days from now” you’ll think about Tuesday, right? But if you add two days from today (remember it starts at 1), it will return 3, so Sunday + 3 it’s Wednesday. It’s a super common mistake that goes unnoticed most of the time, so be careful. Or don’t be careful and use this template 😀.

Return

I’m returning the date in UTC. Why UTC? Because I don’t know what timezone you are and if you convert it straight from the string, you can get problems or invalid dates.

formatDateTime(outputs('Compose_2'),'yyyy-MM-ddThh:mm:ssZ')

When converting, always define your timezone so that you’re sure that the end result is what you expect.

Final thoughts

I wrote this article not because I see these questions asked all the time, and I thought it would help more people understanding how to do it. Please use it as a Power Automate that you call when you need the data so that you don’t need to replicate the steps each time you want a date converted. And, if you opt to do your own based on the steps, it’s perfectly fine, but be aware of the calculation of the date. It’s a super common area of mistakes, so be careful

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 Microsoft Power Automate-related articles here.

Photo by Michael Van Kerckhove on Unsplash

Manuel Gomes

I have 18 years of experience in automation, project management, and development. In addition to that, I have been writing for this website for over 3 years now, providing readers with valuable insights and information. I hope my expertise allows me to create compelling, informative content that resonates with the audience.

View all posts by Manuel Gomes →

You might also like

Power Automate: or function

Power Automate: When an Excel file is created update SharePoint

Power Automate: OneDrive for Business – For a selected file Trigger

14 thoughts on “Power Automate: Convert Excel Number to Date”

  1. Good article. How would you convert a date/time that contains the time as well. For instance, I need to import rows from an Excel column. I cannot control the format of the colums as the file is output from another system. Colum is formatted as General and therefore PowerAutomate sees it as a float in a string. So I need to somehow convert that to an acceptable date/time string the can then be pushed in to a SharePoint list. Hoping you might be able to point me in the right direction! Thanks.

    Reply

    1. Hi Fred,

      Interesting question. Should have added that to the original article.
      I’ll tell you what. I’ll investigate and try to write an article that will explain things in detail.

      I’ll get back to you soon hopefully with a template or something that fixes your problem.

      Sounds good?

      Cheers
      Manuel

      Reply

  4. G’day Manuel

    Can you help with this error whereby the date does not seem to be recognised as a “‘String/date’”?

    Error details:

    The ‘inputs.parameters’ of workflow operation ‘Create_item’ of type ‘OpenApiConnection’ is not valid. Error details: Input parameter ‘item/MeetingDate’ is required to be of type ‘String/date’. The runtime value ‘”43852″‘ to be converted doesn’t have the expected format ‘String/date’.

    This is for a static database in Excel which I only need to import once into a SP List as users will then update the database via SP.

    As it is static I don’t mind creating an additional column in Excel to provide compatible data to the Flow. However, in creating that additional column I have tried formatting the “Meeting Date” data as dates, text, numbers, general; nothing seems to resolve the above error.

    I am familiar with the expression required to convert the string to a date format, but I think the above error needs to be resolve first so that the string is used by the Flow.

    Appreciate any help

    Howard

    Reply

    1. Hi Howard,

      The number 443852 looks like something that Excel would return. You’re trying to use that number to convert it to date, correct?

      In Excel, you should have a “Date” field. Power Automate does all the work. The template will get that number and return it in a date format.

      For the remaining of the Flow, I need to see it to help you further.

      Can you please email me the Power Automate and obfuscate anything private that you can’t share? (email is on the contacts on the top right corner).

      Cheers
      Manuel

      Reply

  5. Thank you for this guide.

    The “subtract by 1” makes logical sense but when I applied it, it resulted in the day after the day I was trying to convert. I corrected by subtracting by 2 and it seems to be accurate, which doesn’t really make logical sense to me.

    Reply

    1. Hey T Brock,

      Indeed it doesn’t make sense. We should try to figure this out, even if it’s working.

      Can you share an example so that we can see what the difference could be?

      Cheers
      Manuel

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Mastodon