cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rosie
Helper II
Helper II

Convert Date as string to Date for Sharepoint list

Hello,

I have read quite a few posts with resolutions about converting a value from a string to a date.  None of the solutions have worked for my situation so I am not sure what is so different.

I am getting the data from a CSV, parsing it into JSON, then either creating or updating an item in a sharepoint list.

The problem only occurs when the date is starting with a number greater than 12.  e.g. 13/01/2021 (dd/mm/yyyy format).

The SharePoint site is set to Time zone (UTS+12:00) Auckland, Wellington.

When I use the date as a string I get the error message, String was not recognized as a valid DateTime

I have tried using the expression  formatDateTime(stringDate, 'dd/MM/yyyy') - I get an error to say it must be in ISO 8601 format.

I then tried using the expression formatDateTime(stringDate, 'yyyy/MM/dd') - I get an error to say it must be in ISO 8601 format.

I have tried splitting up the string into individual parts - Day, Month, Year, then joining them back together and formatting them as dd/MM/yyyy and I get the same error as above. 

formatDateTime(concate(split(stringDate,'\')[2],'/',split(stringDate,'/')[1],'/',split(stringDate,'/')[0],'dd/MM/yyyy') 

If I don't wrap it in the formatDateTime() expression I get an error to say - String was not recognized as a valid DateTime.  So there was no benefit in splitting it apart.

 

There must be away of making this work that I am not understanding.  Can anyone tell me?

PAutomate-formatDate.JPG

 
1 ACCEPTED SOLUTION

Accepted Solutions
tom_riha
Super User
Super User

Hello @Rosie ,

I just tried the expression with reordering the individual parts and it works fine in my flow. Did you try to build only the reordered string, without using the formatDateTime(...) expression, to see what value it creates?

e.g.
concat(split('13/01/2021','/')[2],'/',split('13/01/2021','/')[1],'/',split('13/01/2021','/')[0])

And is it a date only value, without time? If it had time then the split part for year would probably include also the time string.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

View solution in original post

2 REPLIES 2
tom_riha
Super User
Super User

Hello @Rosie ,

I just tried the expression with reordering the individual parts and it works fine in my flow. Did you try to build only the reordered string, without using the formatDateTime(...) expression, to see what value it creates?

e.g.
concat(split('13/01/2021','/')[2],'/',split('13/01/2021','/')[1],'/',split('13/01/2021','/')[0])

And is it a date only value, without time? If it had time then the split part for year would probably include also the time string.



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
Rosie
Helper II
Helper II

Hi @tom_riha ,

Thanks so much that worked! Could have sworn I tried that but obviously not.

 

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
Users online (2,052)