Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

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.
0
Kim Ryan
Asked:
Kim Ryan
1 Solution
 
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
 
gd2000Commented:
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now