Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need help converting a date and time string to actual date and time in excel.

Here is the Format I have now, which is not recognized by Excel as date and time.

Wed Feb 06 05:33:09 GMT 2013
Wed Feb 06 04:52:36 GMT 2013
Wed Feb 06 03:29:39 GMT 2013
Wed Feb 06 03:03:55 GMT 2013

By the way, I want to do this since I need to be able to make a function to display another timezone.

Thanks!

share|improve this question
add comment

2 Answers 2

Try

=DATE(RIGHT(A1,4),MATCH(MID(A1,5,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID(A1,9,2))+MID(A1,12,8)

Or do a Text To Columns with a space as the delimiter and put the bits back together again with a formula (which will probably look very similar to the above).

share|improve this answer
    
Thanks for that works great, It does not convert the time as well though, how would I do that? Thanks! –  user197272 Feb 11 '13 at 18:35
    
Not sure what you mean about the time value. Does the result of the formula produce a date/time value in your system? It does for me. Since you have not clearly specified the desired result, I put the date and time together in the result. If you need the date and the time as separate values, split off the last Mid() function for the time in its own cell. –  teylyn Feb 11 '13 at 19:30
    
On my system Wed Feb 06 05:33:09 GMT 2013 results in 2/6/2013 for example. Thank you very much for your help! –  user197272 Feb 11 '13 at 19:37
    
did you format the cell to show date and time? –  teylyn Feb 11 '13 at 19:48
1  
@user197272: If you ever return to this site, don't forget to accept answers that solve your problem. –  Karan Feb 12 '13 at 17:34
show 1 more comment

Use DateValue to convert the datepart (excluding the GMT and Day)

=DATEVALUE(MID(M13,5, 15))
share|improve this answer
add comment

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.