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.

Chck

Posted 2011-08-03T14:42:32.257

Reputation: 271

Answers

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))

Lance Roberts

Posted 2011-08-03T14:42:32.257

Reputation: 7 895

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.

EBGreen

Posted 2011-08-03T14:42:32.257

Reputation: 7 834

1Might be faster to split by spaces, then just use the cells – soandos – 2011-08-03T14:55:14.960

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 – 2011-08-03T14:57:44.963

Or you could just do it for the whole column manually (its like 5 clicks) – soandos – 2011-08-03T15:01:16.543

That works too. – EBGreen – 2011-08-03T15:05:57.700

This could work, but Lance's formula above is simpler. Thank you for the suggestions, though. – Chck – 2011-08-03T17:57:14.553