Convert Date as string to Date for Sharepoint list

[Rosie]

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?

[tom_riha · Accepted solution]

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.

[Rosie]

Hi @tom_riha ,

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