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?
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:
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'))
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.
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.
- How to: Change SharePoint Default Permissions Group - 16th June 2021
- Solved: BlazorInputFile ‘No File Chosen' Error - 19th May 2021
- Implement Azure Web App Authentication – The Easy Way - 13th April 2021