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 have a date in the format "hh:mm:ss MMM dd yyyy" in a comma delimited text file. I would like to reformat into a workable date for use in excel...how do I do that?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

You can use the following function or a corresponding macro, based on the following algorithm :

Create the "date" using datevalue() function. The input to this would be a string you create from the original format, by keeping only the date portion.

Create the "time" using timevalue()

Add datevalue + timevalue to get the final value.

share|improve this answer

There's no clear answer to your question. It depends on the Regional Settings. For instance, I have my Regional Settings set for Romania, where I live; here, Aug 03 is wrong because the month is after the day. For the US it's the other way around.

Also, Excel has both Date, and Time cells. Maybe you should consider breaking the date and time apart. If you write 8:32:44 it automatically detects it as time for me (hh:mm:ss). For the date, I would suggest using numbers instead of letters (08 instead of Aug); if you write it as 08.09.2009 it automatically detects it as a date (dd.MM.yyyy).

Hope this helps you out!

share|improve this answer

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.