Forum Discussion
Convert string to date
- 4 years ago
Hi, mcflurry
In Power Query with TimeZone:let Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")), Time = Time.FromText(Text.BetweenDelimiters([String], "-", "-")), getTimeZone = Text.AfterDelimiter([String], "T"), getPlusOrMinusZone = Text.Start(getTimeZone, 1), GetHourOfZone = Number.FromText( Text.Middle(getTimeZone,1,2)), GetMinuteOfZone = Number.FromText(Text.End(getTimeZone,2)), DateTime = DateTime.From(Date & Time), DateTimeZone = DateTime.AddZone(DateTime,GetHourOfZone,GetMinuteOfZone), CompleteWithCheck = if getPlusOrMinusZone = "+" then DateTimeZone else DateTime.AddZone(DateTime,-GetHourOfZone,GetMinuteOfZone) in CompleteWithCheckAdd New Column like this. "String" is your column with the provided date. Then just Change Type of the new column to "date" or "datetime" or "datetimezone".
Edit:
I enhanced the code to check whether it's "+" or "-". - 4 years ago
Regional Settings is already to an European country.
Could you please help me on how to fix it with your code?
I guess it's this line, but i don't have the knowledge to achieve it...
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
I'm transforming the the original column with unicode format... and maybe it will work.
Yep, fixed that way, thanks.
Hi, mcflurry
Go to Settings - Regional Settings (current file) and change it to some European country, in my case Czech Republic, or rewrite the script that it first writes month and then day.
Regional Settings is already to an European country.
Could you please help me on how to fix it with your code?
I guess it's this line, but i don't have the knowledge to achieve it...
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
I'm transforming the the original column with unicode format... and maybe it will work.
Yep, fixed that way, thanks.