cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
russianalbert
Frequent Visitor

String was not recognized as a valid DateTime when updating Sharepoint List from Excel

Hello,

I am building a simple flow that would allow me to manually update the date of certifications on an excel sheet and then automatically update that information into a sharepoint list every 1h or so.

The flow is almost complete, however, I am running into a problem when it either creates or updates the cells with the new dates. It always gives the below error message:

 
{
  "status"400,
  "message""String was not recognized as a valid DateTime.\r\nclientRequestId: 1496e158-580d-4bac-a9cb-0bc97d5bfe23\r\nserviceRequestId: 1496e158-580d-4bac-a9cb-0bc97d5bfe23"
}
 
I have a table in excel with the same date and time (in excel I format as "short date") format as the one in sharepoint.
I am attaching below a picture of the entire flow for reference as well. Please let me know if you have encountered such a problem before and any solutions. The error occurs whenever the flow either creates or updates a sharepoint item.
image.png
1 ACCEPTED SOLUTION

Accepted Solutions
v-alzhan-msft
Community Support
Community Support

Hi @russianalbert ,

 

You should use expression below to get the date with type yyyy-MM-dd.

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date']),-2)),'yyyy-MM-dd')

1.png

 

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-alzhan-msft
Community Support
Community Support

Hi @russianalbert ,

 

You should use expression below to get the date with type yyyy-MM-dd.

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['Date']),-2)),'yyyy-MM-dd')

1.png

 

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Alice,

Your solution worked! Thank you very much!!

 

I had another follow up question:

I have my sharepoint linked to my desktop onedrive app. I would ideally like to edit the excel sheet on my desktop from time to time and have those values pushed to the sharepoint.

However what I found is that once I run the flow, if I try to access the Excel sheet from my desktop it gives the below message:

image.png

 

I image that me running the flow is locking out the file somehow. The interesting thing is, when I try to access the same file through sharepoint online, I can edit it no problem on Excel Online.

 

Any ideas how to resolve this lockout issue?

Hi @russianalbert ,

 

I suggest that you don't open the excel when the flow runs since there is no any solution for this issue.

Also please make your post issue as solved.

 

Best regards,

Alice   

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Alice,

 

I never opened the excel while the flow is running, as it would not even run with the excel open.

However I would like to open it at certain times just to be able to edit the data.

But I understand that there doesnt seem to be a solution, but thank you for sharing!

 

Best Regards,

 

Albert

Hi, Alice

This format doesn't work in my flow:

formatDateTime(addDays('1900-01-01',add(int(items('Apply_to_each')?['LIB Global Item Status']),-2)),'yyyy-MM-dd')

GUOGUO_0-1628241986060.png

 

GUOGUO_1-1628242044330.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,436)