cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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:

Create Item.PNG

 

Error:

fError.PNG

 

Data trying to be written to the item:

Field returns.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

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

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

 

View solution in original post

7 REPLIES 7
Highlighted
Helper I
Helper I

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

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?

Highlighted
Community Support
Community Support

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

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

Highlighted
Helper I
Helper I

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

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

Flow1.PNG

Highlighted
Community Support
Community Support

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

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

 

View solution in original post

Highlighted
Helper I
Helper I

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

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.

Highlighted
Frequent Visitor

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

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!).
Highlighted
Advocate III
Advocate III

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

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.

Excel dates if empty.PNG

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (8,296)