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

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 II
Super User II

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 II
Super User II

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

Rosie
Helper I
Helper I

Hi @tom_riha ,

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

 

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (55,720)