cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mnmsymmons
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
v-yuazh-msft
Community Support
Community Support

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
mnmsymmons
Helper I
Helper I

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?

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

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

v-yuazh-msft
Community Support
Community Support

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

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
Frequent Visitor

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
Advocate V
Advocate V

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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,639)