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. It turns out that 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'))
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.