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?

Pstork1
Dual Super User
Dual Super User

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

 

Pstork1
Dual Super User
Dual Super User

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?

Pstork1
Dual Super User
Dual Super User

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.

Pstork1
Dual Super User
Dual Super User

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (6,810)