# Convert “Mon Aug 01 09:08:25 CDT 2011” to a usable date/time using Excel

I have a giant spreadsheet with computer names and the date stamp that they last reported into our PGP server that I need to convert to a usable date/time, so I can sort the column by the time the computer last reported. The format of the field now is as follows:

Mon Aug 01 09:08:25 CDT 2011

All of them have a 3-character day followed by a 3-character month followed by a 2 character date, and so on, so someone with some Excel skill should be able to parse it out.

Edit -- The closest I have come to getting it is using this formula:

RIGHT(L2,4),VLOOKUP(MID(L2,5,3),MonthLookup!A:B,2,FALSE),MID(L2,9,2)

Where L2 is the field I am trying to convert and MonthLookup is a sheet with two columns, the first column being the 3 character month ("Jan", "Feb", etc.) and the second column being the numerical month (1, 2, etc.)

This gives me a usable date, but I need to keep the time as well.

-

Here's the simplest way, though there are others:

``````=DATEVALUE(MID(A1,9,2) & MID(A1,5,3) & RIGHT(A1,4)) +
TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
``````
-
This works perfectly. Thank you, sir. –  Chck Aug 3 '11 at 17:42

Well it's going to depend on what format you want to get as a result. This:

``````=MID(A1,5,15) & " " & MID(A1, 25, 4)
``````

Would give you a result of:

Aug 01 09:08:25 2011

Just play around with MID() to get the parts that you want and use & to concatenate them together in the order that you want.

-
Might be faster to split by spaces, then just use the cells –  soandos Aug 3 '11 at 14:55
Excel does not expose Split() as a cell function. You could do what you are suggestiong, but only by writing it as a macro. –  EBGreen Aug 3 '11 at 14:57
Or you could just do it for the whole column manually (its like 5 clicks) –  soandos Aug 3 '11 at 15:01
That works too. –  EBGreen Aug 3 '11 at 15:05
This could work, but Lance's formula above is simpler. Thank you for the suggestions, though. –  Chck Aug 3 '11 at 17:57