November 28, 2024

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 →

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.

    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

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

    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

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

    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

Leave a Reply

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

Mastodon