Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 0 down vote accepted

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))
share|improve this answer
    
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.

share|improve this answer
1  
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .