• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Text to Date?

AV114

Member
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.
 
I used Power Query/Get and Transform. Here is the Mcode for that action

Code:
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.
 
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")
 
Alternate formula...
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1," ",", ",2)," ",REPT(" ",99),3),99))+0

Apply formatting as needed to result.
 
@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"
Code:
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.
 
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.
Code:
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"
 
Last edited:
Back
Top