Convert string to date

[mcflurry]

Hello guys,

I have a string column with this:

20220307-06:30:06-GMT+0100

I'm trying to convert it to a date column without success.

How could it be done?

[vojtechsima · Accepted solution]

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 "-".

[vojtechsima · Accepted solution]

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 "-".

[mcflurry]

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:

[vojtechsima]

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.

[mcflurry · Accepted solution]

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.