Arranging Dates

G

Guest

I have a group of dates I want arranged chronologically. When I sort on the
column that contains the dates, the application seems to think that 5/27/2007
comes before 6/1/2006. It seems to be keying on the month instead of the
year. How do I need to format the cells in order that the application takes
into account the whole date, not just the month and day?
 
J

Jim Cone

Kevin,
A date is simply a number that is formatted to look like a date.
Excel will sort those date numbers correctly.
In your case Excel is viewing your entries as the actual text not a number.
To confirm this, widen your column slightly and see if the entries
align to the left. Text aligns left.
Ideally, the cells should be formatted as "Date" before the data is entered
using Format | Cells | Number (tab).

You could use an adjoining column, with the formula...
= DateValue(B5) ' using the appropriate cell
Then sort using the new column as the sort key.

You can also try changing the column format to Date and see if this corrects the problem.
It not, try forcing the conversion by copying a BLANK cell, selecting your data
and using Edit | Paste Special | Add. You will have to then format the cells again.

*Of course, if you are extracting data from another source, the data should be
"made nice" before trying to use it...with the trim and clean functions.
**Please make a copy of your data to experiment with, do not use your
original worksheet.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Kevin" <[email protected]>
wrote in message
I have a group of dates I want arranged chronologically. When I sort on the
column that contains the dates, the application seems to think that 5/27/2007
comes before 6/1/2006. It seems to be keying on the month instead of the
year. How do I need to format the cells in order that the application takes
into account the whole date, not just the month and day?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top