Converting from Text to Date

[Thiyags]

Hi I need to convert the following text into Date

Fri Nov 25 05:17:22 +0000 2016

Any idea

[v-sihou-msft · Accepted solution]

@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,

[Thiyags]

@v-sihou-msft

Thanks for ur reply. This works perfectly

[MarcelBeug]

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