Sorting dates problem

[Chartman of the Bored]

I've got a column with several thousand rows of dates that I want to sort from earliest to most recent. The text is in the form mm/dd/yy but the data was imported from another source and formatted as Numbers. In other words, they look like dates, but don't act like dates. No matter how I format them (as numbers or dates), the order gets messed up when I sort them. Any ideas?

Thanks in advance.

[Asala42]

G'day,

copy a 0 in a cell (or a blank cell), select the range of dates, then do an edit/paste special/add. Reformat the cells with a date format.

Adam

[Yogi Anand]

You can coerce all your apparent dates into true dates,by

first clicking on a cell with 0 value, then EDIT|COPY

then highlite the apparent date set and EDIT|PASTE_Special|operation Add

then sort

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:13 ]

[Aladin Akyurek]

Is it not rather the opposite?

If =ISNUMBER(a-date-cell) gives FALSE, select the column range that looks like date... activate Data|Text to Columns.

[Chartman of the Bored]

Thanks to all of you. I tried both methods (adding zeros and text to columns) and they both seem to work.

I understand why adding zero ultimately works --- it seems to turn the cell into a true number. But why does Excel allow users to add anything to something that looks like 4/12/95 ???

And I don't have a clue why the text-to-columns method works.

Just curious. Thanks again.

[Yogi Anand]

On 2002-05-14 10:15, Chartman of the Bored wrote:
Thanks to all of you. I tried both methods (adding zeros and text to columns) and they both seem to work.

I understand why adding zero ultimately works --- it seems to turn the cell into a true number. But why does Excel allow users to add anything to something that looks like 4/12/95 ???

And I don't have a clue why the text-to-columns method works.

Just curious. Thanks again.

But why does Excel allow users to add anything to something that looks like 4/12/95 ???

Excel coerces numeric items that may be presentable as text into true numbers, by addition of 0, or you can accomplish the same thing by multiplying with 1

key in ="4/12/95"+0 in a cell and the "4/12/95" date which was a text string comes to life as true date

And I don't have a clue why the text-to-columns method works.

when you use the Text to Column conversion, Excel looks at items like "4/12/95" and believes you would want to convert them into Number general format or Date format, and it proceeds on that basis -- you could optionally have Excel consider these as text strings if you wanted to.

Hope This Helps

Regards!


[ This Message was edited by: Yogi Anand on 2002-05-14 10:47 ]

[Chartman of the Bored]

Makes sense. Thanx.