Forum Discussion

mcflurry's avatar
mcflurry
Helper I
4 years ago
Solved

Convert string to date

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's avatar
    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 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's avatar
    mcflurry
    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.