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
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 CompleteWithCheck
Add 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 "-".
- mcflurry4 years agoHelper I
Hello vojtechsima
I have a problem with your code.
It looks like it's taking the day as month and the month as day, and imported data from yesterday fails, i've tried with 02/28 as an example:
- vojtechsima4 years agoSuper User
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.- mcflurry4 years agoHelper I
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.