Solved

# Excel: converting formatted dates and sorting

Posted on 2002-05-29
461 Views
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.
0
Question by:Kim Ryan
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 143

Expert Comment

ID: 7041127
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

LVL 44

Expert Comment

ID: 7041261
Hi Teraplane, are these string values or real dates? :O)Bruintje
0

LVL 1

Accepted Solution

gd2000 earned 100 total points
ID: 7041361
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

LVL 19

Author Comment

ID: 7043318
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

LVL 19

Author Comment

ID: 7043410
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
###### Suggested Courses
Course of the Month8 days, 4 hours left to enroll