date sort

[Pammy]

I have a column with a short date formatted: 1/1/08, 10/20/07, showing
this way.

Question: It does not sort correctly, I am finding some 12/02/07 example
mixed in with 1/20/08 and as I look down through the worksheet there are
several 07 mixed in with the 08, how do I correct this?

Thanks,

 

[Pammy]

this is excel 2003

 

[RagDyeR]

Sounds like perhaps some of the dates are *not* true XL recognized dates.
Some may be text that just look like numbers.

When you start the sort, do you get an error dialog window, asking about
what to do with text numbers?

I get that on my XL02 machine.
Should be in XL03 also.

You can test some of the "bad" cells to see if
=Isnumber() comes up "False".

 

[Kevin B]

It sounds like some of your dates are text entries and not dates. When you
did the sort did you get a SORT WARNING dialog box asking if you wanted to
sort numbers and numbers stored as text separately?

Setting the option button to sort anything that looks like a number as a
number will solve this issue, otherwise you'll have to convert the entries to
true dates.

 

[David Biddulph]

It sounds as if (for some cells at least) you've got text, rather than
dates. Format the Cells and see whether they will change to 24 July 08, for
example. If the Format doesn't change the display, the contents are text,
not dates, hence they won't sort correctly.