inconsistent ISO 8601 format

[nvpc]

I have tried building a flow that reads an excel with some date columns into sharepoint list,

below is the excel data and the data download from the flow, some dates do convert but others do not therefor my flow is not working. Any help is appreciated. Thanks

[v-duann-msft]

Hi @nvpc 

Thank you for posting.

According to your description, you would like to  copy date from excel to SharePoint list but is facing issue caused by empty value as well as date conversion. If any misunderstanding, please kindly let me know.

If that’s the case, I recommend you can modify flow using condition. I just create one condition to judge one column. But for your scenario, you may need more condition actions.

My excel:

My flow:

empty(items('Apply_to_each')?['Contract End Date'])

formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['Contract End Date'])),'yyyy-MM-dd')

Once tested, I’m able to copy all records to list.

Hope the content above may help you.

Best regards,

Anna

[nvpc]

Thank you, This will work for the fields that do not come over as dates, so I will try this. The issue is that some columns come over as dates and others as numeric values. I puzzle as to why this is, two columns with the same date 5/5/2020, one converts as a date and the other as numeric value.

[v-duann-msft]

Hi @nvpc 

Thank you for update.

If that’s the case, I don’t think using flow is the best method for you. We can try below method.

Go to excel file and change type of all date column and format them as MM/dd/yyyy.

After that, copy the excel data excluding the title and go back to target SharePoint list. Click on ‘edit in grid view’ and then you will see a “datasheet” view.

Paste directly and all data will be copied to list without issue. No need to do date conversion and no need to worry about empty value.

https://blog.mydock365.com/import-excel-data-to-existing-sharepoint-list/

Hope it helps.

Best regards,

Anna

[nvpc]

thank you, I was aware of this work around but I'm trying to automate this process as I have 6 daily reports with a few hundred records each that need to be converted into a SP list on a daily basis.

[VarshneyBishu]

Did you get an answer to this because I am also facing same issue, All other Date and Time columns in my excel are picked correctly except only 1 column which is coming as integer and not ISO 8601 format. I am frustrated as everything with the column is same. Anyone if you can help please