Convert Text to Date?

[AV114]

I have a column of dates (column A) stored as text in the format

Jun 2 2020 12:00AM Jun 20 2020 12:00AM

which I'm trying to convert to dates in the format (dd/mm/yyyy), ultimately so that I can do calculations

Any suggestions would be appreciated.

 

[AlanSidman]

I used Power Query/Get and Transform. Here is the Mcode for that action

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Parsed Date" = Table.AddColumn(Source, "Parse", each Date.From(DateTimeZone.From([Column1])), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Parsed Date",{"Column1"})
in
    #"Removed Columns"

Bring the data into PQ Editor. Add a column and Parse the Date. Remove the uneeded column.

 

[Juniad]

Formula solution.
Suppose your text in A1 type this formula.
=TEXT(MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1))&"/"&LEFT(A1,SEARCH(" ",A1)-1)&"/"&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,4),"dd/mm/yyyy")

 

[Chihiro]

Alternate formula...
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",", ",2)," ",REPT(" ",99),3),99))+0

Apply formatting as needed to result.

 

[Chihiro]

@AlanSidman

If using PQ, there's no need to add custom column when "mmm" is included in typical datetime string that can be handled by implicit conversion (i.e. without specifying culture "en-US" etc).

It just requires Type conversion done in two steps, first to datetime, then to date.
Ex: Below will work for both patterns.
1. "mmm d yyyy h:mmAM/PM"
2. "d mmm yyyy h:mmAM/PM"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type date}})
in
    #"Changed Type1"

Culture must be specified when "mmm" isn't used and only "mm" is used.

 

[GraH - Guido]

That's a nice insight you share @Chihiro ! Logical too, yet I never gave that a thought.

Alternative PQ is extracting the date via the UI.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Column1", DateTime.Date, type date}})
in
    #"Extracted Date"