convert text to date (flow to sharepoint list)

[MrtnM]

Hi,

I would like some tips on how to convert a text string to a date.

I have built a Flow that sets a string variable from an email, looking like "12/3 2021" that I want to convert to a date to put into a Sharepoint date column. In this case, 12th of March 2021.

Any tips?

[Pstork1]

All you really need to do is get it into an ISO 8601 formatted string.  So just replace the space between day and year with a '/' and run the result through a FormatDateTime() function.

formatDateTime(replace('12/3 2021', ' ','/'),'o')

[MrtnM]

Thanks! One more thing: in Swedish dates, we write "day/month year", meaning 12/3 2021 is 12th of March. But the formatdatetime treats this as month/day/year - that is 3rd of December. Any workaround for this?

[Pstork1]

The 'o' means it outputs the date as ISO 8601 UTC which should output as 'yyyy-mm-ddThh:mm:ss.xxxZ

When you put that into SharePoint it should come out as however the localized date in SharePoint is for display.  But the input should be ISO 8601.  If your locale is set to Sweden then the input should be formatted however its formatted locally.  I'm in the US so my sample is formatted for US locale.

[MrtnM]

Hi,

maybe I misunderstood, but this is what happens in the Flow:

string variable is formatted with the day and month in the wrong place 

and then in sharepoint, thje date is shown correctly from the date formatting, but incorrectly compared to the original plain text date

[Pstork1]

That is dependent on the locale settings for your flow environment.  Double check what those are in the flow settings and where your environment is located.

[MrtnM]

Thanks, but I cannot find any such settings, not in the Flow itself nor in the environment? Where are they suposed to be located?

[Pstork1]

In the Power Automate studio go to the Power Automate Settings on the gear menu.

[MrtnM]

Ah, that settings menu 🙂

It was set to English/GB, but changing to Swedish/Sweden didn't make any difference. The date time still "misplaces" day and month (also meaning that a the plain text string 22/3 2021, that is 22nd of March, renders an error in the time date format)

EDIT: I noticed that, if I change the initial string "22/1 2012" to "22 jan 2021", the correct date 22nd of January is rendered in the format date time. BUt it would be nice to be able to use d/m.

[Pstork1]

You'll probably need to check the regional settings for SharePoint as well.