cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mnmsymmons
Level: Powered On

'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
Community Support Team
Community Support Team

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

6 REPLIES 6
Highlighted
mnmsymmons
Level: Powered On

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?

Community Support Team
Community Support Team

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

mnmsymmons
Level: Powered On

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

Community Support Team
Community Support Team

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

mnmsymmons
Level: Powered On

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.

DAGEM
Level: Powered On

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!).

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users Online
Currently online: 251 members 4,536 guests
Please welcome our newest community members: