Problem
Dealing with dates can be a real pain in the proverbial sometimes … and Microsoft Power Automate (aka Microsoft Flow) is no exception.
I recently had to bulk load some records from an Excel file into SharePoint Online using Microsoft Power Automate … you’d be forgiven to think that this would be a trivial task … WRONG!
All was going well until … I encountered date fields in my Excel file … then all hell broke lose!
I was puzzled why Dates in my Excel file were processed by Microsoft Power Automate as numbers … what the? What’s going on here?
Solution
The root problem is that Microsoft Power Automate reads Date columns as Text. Excel stores dates in Serial Date format which means that a date like ‘2019-01-10’ will be read as ‘43475’ within Power Automate.
You can read more about how excel handles dates here:
http://www.cpearson.com/excel/datetime.htm.
Therefore we need to convert the Number to a Date using the below expression within our Power Automate workflow
// Format
if(empty(<DATE TO CONVERT>),null,addDays('1899-12-30',int(<DATE TO CONVERT>),'yyyy-MM-dd'))
// Example
if(empty(item()?['Start Date']),null,addDays('1899-12-30',int(item()?['Start Date']),'yyyy-MM-dd'))
Example
In this simple example I’ll create a workflow to insert the contents of an Excel spreadsheet into a HTML table.
The flow contains the following steps:
1. Read rows from an Excel Spreadsheet
2. Insert each row into a HTML formatted table
I’ve added the below expression into the ‘FormattedDateCompleted’ field of the HTML table.
if(empty(item()?['Timesheet Completed Date']),null,addDays('1899-12-30',int(item()?['Timesheet Completed Date']),'yyyy-MM-dd'))
Here is the output after running the workflow. You can see the original date value and the formatted date value.
Final Thoughts
Well I hope this solution has helped you. If you have any thoughts or alternative solutions feel free to post them in the comments below.
This blog is my place to share my thoughts, helpful solutions and just random nerdy stuff.
- Solved: Build Errors Not Showning in VS 2022 - 21st November 2024
- How To: Configure VSCode to Trust Self-Signed Certs - 16th August 2024
- Solved: GitHub Actions – HTTP 403: Resource not accessible by integration - 13th June 2024
25th October 2019 at 1:20 pm
Great Article! Thank a lot for your help!
4th November 2019 at 4:10 pm
Hi Shane – I’m just getting into Flow. Where exactly did you make the change? Can it be made at the spreadsheet/table level ?
4th November 2019 at 9:48 pm
Hi Robert, you’ll need to create the Expression within Microsoft Flow via the popup menu that appears when you click a field/step in your Flow.
Otherwise, (although I haven’t tested this) you could convert the ‘date’ formatted cell to a ‘text’ type field and it should import without issue.
15th November 2019 at 10:11 am
Hi Shane, Thanks for the tip but how to convert/format date and time?
27th January 2020 at 8:27 pm
Shane,
I thank you profusely for this! You have saved me a lot of head scratching!
Dan
2nd April 2020 at 9:05 pm
anybody here that can explain to me how to get this configured in Power Automate ?
3rd April 2020 at 3:31 am
Great article! btw, how to create an expression if i have date and time in excel, (Example: 43924.04097)
18th July 2020 at 3:58 pm
Sorry but this doesn’t work for me. I just get an error stating ‘The Expression is Invalid’. Any ideas what I need to check.
19th July 2020 at 8:26 am
Hi John,
Thanks for pointing that out. I just realised there was a mistake in the expression, which I’ve now corrected.
I’ve also included an example for reference. Hope that helps.
19th July 2020 at 3:33 pm
Great. Thanks. I’ll take a look.
6th August 2020 at 7:39 am
I am not able to get an html table column from a MM/DD/YY HH:MM formattedExcel column. I have no empty cells in the spreadsheet that are in the date column.
I’ve tried your if / empty/ null but no luck.Column2 is my date column (see failure reason below).
The execution of template action ‘Select’ failed: The evaluation of ‘query’ action ‘where’ expression ‘{
“App Owner”: “@item()[‘Application Owner’]”,
“Change Owner”: “@item()[‘Change Owner’]”,
“Snap Size GB”: “@item()[‘SnapSizeGB’]”,
“Snap Name”: “@item()[‘SnapName’]”,
“Snap Description”: “@item()[‘SnapDesc’]”,
“VM Name”: “@item()[‘VMName’]”,
“V Center”: “@item()[‘VCenter’]”,
“Column2 Date”: “@if(empty(item()?[‘Column2’]),null,addDays(‘1899-12-30’,int(item()?[‘Column2′]),’yyyy-MM-dd’))”
}’ failed: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.
Column2
2/6/19 6:43 AM
8/30/19 1:16 PM
5/27/20 3:54 AM
5/27/20 2:50 PM
5/27/20 2:50 PM
9/26/19 11:31 PM
10/10/19 11:34 PM
2/7/20 8:24 PM
5/19/20 3:12 PM
5/19/20 3:13 PM
6/11/20 11:08 PM
6/11/20 11:09 PM
8th August 2020 at 8:12 am
The reason you encounter an error is because the above expression expects whole numbers only (i.e. an integer) however the time portion of the date/time stamp is returned as a fraction.
For example,
’10/7/2020′ will show as ‘44111’.
’10/7/2020 2:00′ will show as ‘44111.0833333’.
You can use the below expression to strip out the time component and return the date portion only.
if(empty(item()?[‘Column2’]),null,addDays(‘1899-12-30’,int(split(item()?[‘Column2’], ‘.’)[0]),’yyyy-MM-dd’))
10th August 2020 at 8:56 pm
Great work Shane! cheers!
20th August 2020 at 12:21 am
Hi Shane! This helps in a great way, but one question: when I run the Flow, the date it delivers is always a day BEHIND what the actual date should be. For example, if the designate date is August 30, it posts August 29 in the results.
Further, since I’m creating a flow from Excel to Planner, it will post a new task with the Aug 29 date, but in the Planner schedule, it posts the Aug 30 date.
Since this issue doesn’t occur when I create a task directly in Planner, I have to assume that the problem lies with how the date is being interpreted during the Flow.
Any suggestions? Thanks in advance!
-Matt
20th August 2020 at 8:39 pm
Hi Matt,
While I haven’t come across this issue myself I suspect it could be related to a timezone conversion issue.
Try convert the date to the correct timezone.
E.g., )
convertFromUtc(
convertFromUtc(utcNow(),’W. Australia Standard Time’)
9th September 2020 at 10:10 pm
Saved my day. Thank you.
4th November 2020 at 7:01 am
This is exactly what I was looking for. Thank you!
7th January 2021 at 6:08 am
I am trying to figure out why I keep receiving errors for null values. After converting the string I am updating a sharepoint list but the flow keeps failing out on the field I have converted. “cannot read push of undefined” because of the null value. Any ideas?
8th January 2021 at 9:34 pm
It finally worked! It was user error (me) lol thanks for the help this saved me!
22nd January 2021 at 10:51 pm
There is another simple solution that works for me. Add a column in your original excel table that is the date column formatted as text. A formula like Text([Your Date Column], “dd/MM/yyyy”) should work. This will keep the formatting of your date column but turn it into a text value. When you create your flow use this Text date column instead of converting the date column.
26th May 2021 at 10:32 pm
Excellent information! Solved our ETL load problem on a date. Thank you!
22nd July 2022 at 7:08 am
Thank you so much mann!!!
15th March 2023 at 1:19 pm
Thank you it was really helpful