Excel: converting formatted dates and sorting

I have a column of dates in the format od ddd mmm dd hh:mm:ss yyyy, for example:
Thu May 21 11:45:21 2001
How to convert them to numeric values so I can sort by this column? Using Excel 97.
LVL 19
Kim RyanIT ConsultantAsked:
Who is Participating?
 
gd2000Connect With a Mentor Commented:
Say the info is in cell A3.

Define     Space1 =FIND(" ",$A$3,1)
     Space2 =FIND(" ",$A$3,Space1+1)
     Space3 =FIND(" ",$A$3,Space2+1)
     Space4 =FIND(" ",$A$3,Space3+1)

Then the day is =MID(A3,Space2+1,2)
The month is =TRIM(MID(A3,Space1,Space2-Space1))
The year is =RIGHT(A3,4)
The time is =TRIM(MID(A3,Space3,Space4-Space3))

The combined value is =day&"-"&month&"-"&year&" "&time
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the format is date, Excel will sort properly by the date values. If the value is a pure string representing a date, then you need to get the true date value from that string (use cdate() function)...

CHeers
0
 
bruintjeCommented:
Hi Teraplane, are these string values or real dates? :O)Bruintje
0
 
Kim RyanIT ConsultantAuthor Commented:
Yes, they are real date values. CDATE diesn't seem to be availabe on my version, only DATEVALUE, which can't handle these long formats.
0
 
Kim RyanIT ConsultantAuthor Commented:
Thanks gd2000, it worked fine. Has MS every considered using regular expressionsin Excel? You could then reduce all of these steps into 1 simple pattern.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.