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?
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.
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!