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.
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')
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?
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.
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
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.
In the Power Automate studio go to the Power Automate Settings on the gear menu.
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.
You'll probably need to check the regional settings for SharePoint as well.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Read the latest about new experiences and capabilities in the Power Automate product blog.
At the monthly call, connect with other leaders and find out how community makes your experience even better.
If you are a small business ISV/Reseller, share your thoughts with our research team.