How to convert date from "Fri Aug 22 13:49:20 EDT 2003"

[Dan Harlan]

Need to convert a bunch of text files that include dates in the "Fri Aug 22
13:49:20 EDT 2003" format. Have been unable to break them into separate
columns during import as the other columns are all tab delimited. Wish to
convert this into a standard date/time format. How best to achieve this as I
have numerous files to do this with. TIA, ~Dan

 

[CLR]

Well, this is a bit clumbersome, but it does the job.
First, create a VLOOKUP table...I used J1:k12, with the three letter months
down column J and the corresponding month number down column K. Then,
assuming your string is in A1, put this in B1 and copy down...........

=DATE(RIGHT(A1,4),VLOOKUP(MID(A1,5,3),$J$1:$K$12,2,FALSE),MID(A1,9,2))

hth
Vaya con Dios,
Chuck, CABGx3

 

[Bernard Liengme]

Using Data|Text to Column, I got this in columns A thru F
Fri Aug 22 13:49:20 EDT 2003

I believe you also have this
In G1 I entered the formula
=DATE(F1,MATCH(B1,{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0),C1)+D1
which gave me 22/08/2003 13:49 (I use Canadian date format; you will get
month & day in US format)
best wishes

 

[Earl Kiosterud]

Dan,

If you're opening, changing, and saving the files by hand, change the extensions of the
input files to .txt. Then use File - Open. It will start the Text Import Wizard, where you
can specify Delimited, The tab delimiter, and other stuff. Then change the formatting
(Format - Cells - Number) of the column with your dates/times. Then save the file, setting
the file type as *.txt or *.csv.

The import, since it's tab-delimited, will not parse that unusual date field. But you can
do that with the column that it winds up in with formulas in other columns. If your "Fri
Aug 22 13:49:20 EDT 2003" is in column B, you could use

=DATEVALUE(MID(B2,5,6)& ", " & RIGHT(B2,4))

The day (22) must always be 2 characters for this to work properly. It might need some
tweaking. Format the cell containing the formula as a date (Format - Cells - Number -
Date).

If the layout of all the text files is the same, you'll save time by setting up an Import
Query (Data - Import external data - Import Data). That will import the various files into
the sheet containing the formula that builds a new date, which you can copy/paste using
paste special values, from the formula cell to where you want it.

Or you could use Data - Text to columns on that column alone using space as the delimiter.
Then go after the parts you want. This sounds like macro time, once you find a good
algorithm, since you have many to do.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[David Biddulph]

=--(MID(A1,9,2)&"/"&MID(A1,5,3)&"/"&RIGHT(A1,4)&" "&MID(A1,11,9)) and format
appropriately

 

[Rick Rothstein \(MVP - VB\)]

Need to convert a bunch of text files that include dates in the "Fri Aug

22
13:49:20 EDT 2003" format. Have been unable to break them into separate
columns during import as the other columns are all tab delimited. Wish to
convert this into a standard date/time format. How best to achieve this
as I
have numerous files to do this with. TIA, ~Dan

If you are up for a VBA macro solution, then give this subroutine a try...

Sub FixDateTime()
Dim R As Range
Dim SelectedCells As Range
Dim Parts() As String
Const DatePattern = "[SMTWF][uoehra][neduit] [JFMASOND][aepuco]" & _
"[nbrylgptvc] [ 0123]# ##:##:## [ECMP][SD]T ####"
If Selection.Count = 1 Then
Set SelectedCells = ActiveCell
Else
Set SelectedCells = Selection
End If
For Each R In SelectedCells
If R.Value Like DatePattern Then
Parts = Split(R.Value, " ")
R.Value = CDate(Parts(2) & " " & Parts(1) & " " & _
Parts(5)) + CDate(Parts(3))
End If
Next
End Sub

Simply select the cells you want to change and run the macro.

Rick

 

[Dan Harlan]

Thansk everyone. I used Earls solution as it was the shortest. Really
appreciate everyone's quick help.

 

[David Biddulph]

If you go down the text to columns route you can probably skip the MATCH()
function and get away with
=--(C1&"-"&B1&"-"&F1)+D1 or
=--(C1&"/"&B1&"/"&F1)+D1 or
=DATEVALUE(C1&"/"&B1&"/"&F1)+D1 or ...