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.
- Get the number
- Start a variable with the reference date (1st January 1900)
- Add the days to that date
- 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
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.
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
Hi Fred,
I just wanted to let you know that I wrote another article that describes how to deal with the date:
https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date-to-the-second/
I hope this helps you!
Cheers
Manuel
i have the same requirement as fred, can you find a solution for your problem @Fred? thx
Hi dhakersoui,
I just wanted to let you know that I wrote another article that describes how to deal with the date:
https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date-to-the-second/
I hope this helps you!
Cheers
Manuel
Manuel , you are great!! I have used several of your blogs in my solutions!
Hi Graciela,
You’re too kind. You just made my day.
I’m glad I’m helping you :).
Anything else, please let me know.
Manuel
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
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
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.
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
Here is a good synopsis of the flow. You will see your expression under “Calculate Closing Date”.
https://powerusers.microsoft.com/t5/Building-Flows/Recurring-Flow-Cannot-Get-Date-Condition-To-Work-As-Intended/m-p/930906/highlight/true#M130661
Power automate is missing day ‘1900/02/29’…
That’s why we need to substract 2 days.
Exacly!