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?

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

4 Replies

  • 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
      Helper 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:

       

       

      • vojtechsima's avatar
        vojtechsima
        Super 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.