Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite

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

2 Answers

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
up vote 0 down vote

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.