'Create Item' from 'List Rows present in table' - String was not recognized as a valid DateTime

[mnmsymmons]

Hi,

I've done a bit of searching and can't quite find a solution that works.

My flow is grabbing all rows in a table in an excel file on SharePoint. It is able to then create an item in a Sharepoint list based on each of these rows until I include any Date fields.

The Sharepoint List format for these fields is "Date & Time" and the format in Excel is Date in "DD/MM/YYYY" based on New Zealand locale (which mirrors the formate setup in SharePoint).

I've attached what the field is returning for the first item and the error message. Based on research I am guessing there might be a Compose action that might fix this, or can I just use the FormatDateTime in the Create Item action itself?

Thanks in advance

Create Item:

Error:

f

Data trying to be written to the item:

[mnmsymmons]

OK, managed to find a work around. Appears to be a problem with either Excel or Flow not providing it in a recognised format for the SharePoint List (despite it mirroring it).

By changing the format in excel from date to text/general in "YYYY-MM-DD" the date went in successfully.

If anyone has a way of turning a date into the above format so I don't have to interfere with the data source that would be great?

[v-yuazh-msft]

Hi @mnmsymmons,

Could you please share a screenshot of the configuration of your flow?

Could you please take a try to save the Excel in your Onedrive for business folder instead of save it in the sharepoint, and make a test to see if the issue still exists.

Best regards,

Alice

[mnmsymmons]

Thanks @v-yuazh-msft

I am unable to setup this flow with the file in One Drive for Business due to current access limitations.

I've attached the previous action which is also the first in this flow (list rows in a table), nothing other than this and the screenshot on the original post for the Create Item setup. The HTTP trigger is raised from another flow, I separated the flows out for previous problem solving but no data is passed through.

Thanks

[v-yuazh-msft · Accepted solution]

Hi @mnmsymmons,

The date time in excel table in sharepoint would always be formated to the string as your screenshot in microsoft flow currently.

I afraid that there is no way to achieve your needs in Microsoft Flow currently if you save the excel table in sharepoint.

Best regards,

Alice

[mnmsymmons]

thanks @v-yuazh-msft

Just tested by using Get Rows for file when save in OneDrive and as you indicated this works with no problem.

[DAGEM]

This article mentions about converting date formats:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Formatting-Date-And-time-in-MS-Flow/m-p/...

Also, I have a flow which formats the date when adding days (I'm checking for events in the future) which uses the following code: 

addDays(utcNow(),14,'yyyy-MM-dd')

Sure there's plenty of other ways of acheiving what you want too (probably better than my way!).

[RachelRig]

In case anyone out there is looking for a solution to this. Please see: Convert Excel date value 

I resolved this using a Compose expression referenced there:

if(empty(items('ApplyEach_Dates')?['Work Date']),'',addDays('1899-12-30',int(items('ApplyEach_Dates')?['Work Date']),'MM/dd/yyyy'))

In this example, I have 2 date columns, "Work Date" and "Week-Ending". Set up a Compose action for each date, then plug the output of those actions into the SharePoint columns.