cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrtnM
Helper V
Helper V

convert text to date (flow to sharepoint list)

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?

9 REPLIES 9
Pstork1
Dual Super User
Dual Super User

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')


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
MrtnM
Helper V
Helper V

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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 

fl.png

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

sp.png

 

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

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

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
MrtnM
Helper V
Helper V

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (4,703)