Convert Text to Dates with Flash Fill – Data Cleansing Challenge

The Flash Fill is clearly the most elegant (and fastest). Another method:
1. Use Text to Columns to get separate columns for each “word:” Tue Mar 14 19:09:37 CDT 2017
2. Use concatenation to create a “pseudo-date” (one that looks like a date but is text): =B2&” “&C2&”, “&F2 = Mar 14, 2017
3. Multiply the result by 1 to convert it into a date. (Excel usually takes numbers formatted as text that are used in a formula and converts them into numbers.)
4. Time converts directly. Add the date and time columns and format as you describe.

I used =DATEVALUE(TEXT(MID(A2,9,2)&” “&MID(A2,5,3)&” “&RIGHT(A2,4),”mm/dd/yyyy”))+TIMEVALUE(MID(A2,12,8))

=DATEVALUE(MID(A1,9,2)&”-“&MID(A1,5,3)&”-“&RIGHT(A1,4))

Great brain teaser, thanks! 😀
My answer is maybe not the fastest one but it works:
Column B: MONTH /=VLOOKUP(MID(A2,5,3),Months!A:B,2,0)/ I’ve added additional sheet “Months” with names in one column and numbers in another
Column C: YEAR /=RIGHT(A2,4)*1/
Column D: DAY /=MID(A2,9,2)*1/
Column E: DATE /=DATE(C2,B2,D2)/
Column F: TIME /=MID(A2,12,8)*1/
Column G: Converted Date & Time /=E2+F2/
Column H: Check /=VLOOKUP(A2,Explanation!A:B,2,0)-G2/ I’ve checked all the results with “Explanation” sheet and got all zeros, yeeeessss! 😀

I had an extra problem using the Dutch version of Excel: the name of the month is not recognized!
I first used text to columns (fixed width) to separate, skipping the name of the day and CDT.
Then I moved the column with the time to be the last column.
Result:
A B C D
2 Mar 14 2017 19:09:37
3 Jun 10 2017 03:21:15
etc.

Then I made a table for numbering the names of the months in 2 columns
M N
1 Jan 1
2 Feb 2
3 …

Then I used the DATE-function to create a date from the Year-column, the Month-nale (using Vlookup to change the name into a value) and the Day-column.
=DATE(C2;VLOOKUP(A2;@M$1:$N$12;2;FALSE);B2)
I copied that column down for the whole table.

Select the column with the created dates, copy it, paste values in the same place.
Then delete the columns A,B and C and the lookup table.

Hi,
Here is one solution, which does not depend on regional settings:
=DATE(RIGHT(A3,4),MATCH(MID(A3,5,3),{“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”},0),MID(A3,9,2))+VALUE(MID(A3,12,8))

Match gives you an index number based on the three letter found in text from the 3rd to the 5th position defined by MID.
Cheers
Attila

(Excel 2003)

First, I extracted texts for day, month, year, and time:

“DAY” (B2) =MID(A2,9,2)
“MONTH” (C2) =MID(A2,5,3)
“YEAR” (D2) =RIGHT(A2,4)
“TIME” (E2) =MID(A2,12,8)

Next, calculated the date and time values:

“DATEVALUE” (F2) =DATEVALUE(CONCATENATE(B2,”-“,C2,”-“,D2))
“TIMEVALUE” (G2) =TIMEVALUE(E2)

Finally, combined the two:

“DATE & TIME” (H2) =F2+G2

Apparently, there could have been less formulas but I prefer small steps at a time.

Flash fill is fine, but with these data it works. But if there are more years it will not work. Change some of the years into 2016 of 2018 and it will not work….

Workaround:
Start with typing the year, then the month and then the day (2017-Mar-14)in a new column.
Use Flash fill

End with:
Select the column and choose Data > Text to Columns. Then only change the last step selecting Date = YMD and it will work.

I first used the formula =DATEVALUE(MID(A2,9,2)&MID(A2,5,3)&RIGHT(A2,4))+TIMEVALUE(MID(A2,12,8))

I then had a go with Powerquery which is nice and neat.
The method I used within PowerQuery was:

1 convert the raw data to a table (“Tbl_RawData”)
2 import using Data / From Table Range
3 Split the data by Delimiter “Space” all occurrences
4 Merge the new columns containing Day, Month, Year & time columns
5 Change the new columns data type to Date & Time
6 Finally, I renamed the Query to “Process raw dates”
7 “Close and Load to”

The contents of the Editor is below:

let
Source = Excel.CurrentWorkbook(){[Name=”Tbl_RawData”]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date & Time Stored as Text”, type text}}),
#”Split Column by Delimiter” = Table.SplitColumn(#”Changed Type”, “Date & Time Stored as Text”, Splitter.SplitTextByDelimiter(” “, QuoteStyle.Csv), {“Date & Time Stored as Text.1”, “Date & Time Stored as Text.2”, “Date & Time Stored as Text.3”, “Date & Time Stored as Text.4”, “Date & Time Stored as Text.5”, “Date & Time Stored as Text.6″}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Date & Time Stored as Text.1”, type text}, {“Date & Time Stored as Text.2”, type text}, {“Date & Time Stored as Text.3”, Int64.Type}, {“Date & Time Stored as Text.4”, type time}, {“Date & Time Stored as Text.5”, type text}, {“Date & Time Stored as Text.6″, Int64.Type}}),
#”Merged Columns” = Table.CombineColumns(Table.TransformColumnTypes(#”Changed Type1″, {{“Date & Time Stored as Text.3”, type text}, {“Date & Time Stored as Text.6”, type text}, {“Date & Time Stored as Text.4”, type text}}, “en-GB”),{“Date & Time Stored as Text.3”, “Date & Time Stored as Text.2”, “Date & Time Stored as Text.6”, “Date & Time Stored as Text.4″},Combiner.CombineTextByDelimiter(” “, QuoteStyle.None),”Merged”),
#”Changed Type2″ = Table.TransformColumnTypes(#”Merged Columns”,{{“Merged”, type datetime}}),
#”Removed Columns” = Table.RemoveColumns(#”Changed Type2″,{“Date & Time Stored as Text.1”, “Date & Time Stored as Text.5″}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Merged”, “Date & Time”}})
in
#”Renamed Columns”

Reading through these responses inspired me to think about this problem more, and I came up with what I think is a very elegant solution to this problem. Since we know that these dates are a fixed length we could do something like this (assume that this formula is written in cell C2 for the first row of data and can then be auto-filled to the bottom):
=RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7)

Because you use the “+” operator, you don’t even have to convert the range with any “*1” or any of that business. AND, It would even be possible to make this solution work with FIND() or SEARCH() for text date-timestamps that are not fixed length!

Thanks everyone for the inspiration!!

Went with different idea uses current year only but it seems to work

Highlight column A
DATA
Text to Columns
Fixed Width
Next
Remove or move lines
leaving line on left side of month
leaving line on right side of time
This leaves three columns Click Next
1st column mark do not import
3rd column mark do not import
Click Finish

Highlight column A
Home Tab format cells
Custom
Select or Type
[$-409]m/d/yy h:mm:ss AM/PM;@

Rename A1 if needed
Done

Thanks for the “homework” assignment; it got me back to my Excel training. I wanted to solve it using my limited knowledge without looking anything up. So I did it in a totally convoluted backward way that I could not even begin to explain here (it involved 19 columns), but in the end I got an automated worksheet that allowed me to just copy the first row down. But it was a good exercise and forced me to think in a logical way. When I looked at the much easier ways that other were able to do it, it proved that although there is more than one way to skin a cat in Excel, it’s best to know the shortest way!

Hi Jon,

First I tried Power Query similar to Ali Kassie.

Then, since I like formulas, I placed the following:
=DATEVALUE(MID(A2,5,6)&”, “&RIGHT(A2,4))+TIMEVALUE(MID(A2,12,8))

change formatting to Custom m/d/yyyy h:mm AM/PM

Thanks for the challenge!

Mt

I also went woth VBA:

Function text2datetime(text$)

jaar$ = Right$(text$, 4)

maand$ = LCase$(Mid$(text$, 5, 3))

Select Case maand$

Case “jan”

maand$ = “01”

Case “feb”

maand$ = “02”

Case “mar”

maand$ = “03”

Case “apr”

maand$ = “04”

Case “may”

maand$ = “05”

Case “jun”

maand$ = “06”

Case “jul”

maand$ = “07”

Case “aug”

maand$ = “08”

Case “sep”

maand$ = “09”

Case “oct”

maand$ = “10”

Case “nov”

maand$ = “11”

Case “dec”

maand$ = “12”

Case Else

maand$ = “01”

End Select

Dag$ = Mid$(text$, 9, 2)

tijd$ = Mid$(text$, 12, 8)

datum$ = Dag$ & “/” & maand$ & “/” & jaar$

text2datetime = DateValue(datum$) + TimeValue(tijd$)

End Function

Looks like I am late to this conversation but…
I chose Text to Columns because of my limited knowledge of Excel.
I first selected the entire Column A of data excluding the header
Clicked on the Data Tab and then Text to Columns
In Step 1 of the Text to Columns, I selected Delimited and then Next
In Step 2 I chose “Space” as the delimiter and then Next
In Step 3 I chose “General”, then selected the 1st column and clicked on “Do not import column (skip)”, then selected the 5th column and clicked on “Do not import column (skip)”, and then Finish. Skipping those two columns just saves space.
Another option in Step 3 is “Destination”. I chose cell B2 just to preserve the original data while playing but it defaults to the original cell which in this case was A2 and although it does not list all of the cells highlighted below A2 they are included.

Now I have the month in Column B, day in Column C, time in Column D, and year in Column E.
I placed the following formula in Column F =SUM(DATE(E2,MONTH(1&B2),C2),D2).

You then use formatting to to see the date and time however suits you.
I chose a custom format “dddd mmm, dd yyyy hh:mm:ss” just for fun because it closely simulates the original text.

Obviously this would not work in those areas that do not recognize 3 letter month abbreviations but that is not part of this challenge.

After reading through these answers I really like the way Kent approached it. Very simple.

“Kent – November 16, 2017
Reading through these responses inspired me to think about this problem more, and I came up with what I think is a very elegant solution to this problem. Since we know that these dates are a fixed length we could do something like this (assume that this formula is written in cell C2 for the first row of data and can then be auto-filled to the bottom):
=RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7)

Because you use the “+” operator, you don’t even have to convert the range with any “*1” or any of that business. AND, It would even be possible to make this solution work with FIND() or SEARCH() for text date-timestamps that are not fixed length!

Thanks everyone for the inspiration!!”

Hi Jon,

Regarding Kent’s post on 11/16/17, I’m trying to figure out what the 12 is in his formula =RIGHT(REPLACE(A2,11,13,”,”),12)+MID(A2,12,7). I deleted it to see what would happen and it changes the year from 2017 to 1900. I tried 11 and got an error. I was unfamiliar with the REPLACE and MID formulas so I looked them up and played with them today. Nice to know!
Thank you.
Pam