Forum Discussion

Thiyags's avatar
Thiyags
Helper II
9 years ago
Solved

Converting from Text to Date

Hi I need to convert the following text into Date

 

Fri Nov 25 05:17:22 +0000 2016

 

Any idea

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Thiyags

     

    In this scenario, you can use PATHITEM() function to split the different date parts in the string into columns. Please refer to formulas below:

     

    Month = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),1)
    Day = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),2)
    Time = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),3)
    Year = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),4)

    Then you can concatenate them and convert into datetime type.

     

    Datetime = Table3[Month] & " " & Table3[Day] & " " & Table3[Year] & " " & Table3[Time]

     

    Regards,

3 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    My suggestion would be to split the string.
    Next step is to convert to date depending on your requirements:

    Dated = ignore time zone

    LocalDate = Local date, taking into account the time zone.

    let
    Source = Text.Split("Fri Nov 25 05:17:22 +0000 2016"," "),
    Dated = Date.From(Source{1}&" "&Source{2}&", "&Source{5}),
    LocalDate = Date.From(DateTimeZone.From(Source{1}&" "&Source{2}&", "&Source{5}&", "&Source{3}&" "&Source{4}))

in
    Dated
or    LocalDate
  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Thiyags

     

    In this scenario, you can use PATHITEM() function to split the different date parts in the string into columns. Please refer to formulas below:

     

    Month = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),1)
    Day = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),2)
    Time = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),3)
    Year = PATHITEM(SUBSTITUTE(Table3[Column1]," ","|"),4)

    Then you can concatenate them and convert into datetime type.

     

    Datetime = Table3[Month] & " " & Table3[Day] & " " & Table3[Year] & " " & Table3[Time]

     

    Regards,