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.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

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.

Join me at 365 and PWR EduCon in Chicago
EduCon Chicago 2022

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

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

Users online (1,941)