Power Automate: How to convert string to date format. Trying to import excel from OneDrive to SharePoint list

Question

I am trying to import excel file from OneDrive to SharePoint list using Power Automate. It is working as expected for all columns except the date column. I am not able to convert date string to date format. please help.

Answer 1

I read about the issue with Dates in Excel for Power Automate. Seems like this is know challenge and I myself tried steps from the blog and I could get to convert date as expected.

Go through this blog and you will have an idea how to work with it.

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'))