Power Automate: Convert Excel Number to Date

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