why does date sort as text?

D

dzelnio

I have a bunch of dates like 04/15/07.

They sort like text looking putting all the months in order when I
need the date in order. I needs to look at year first, then month,
then day.

Command 1 shows that the column is defined as a date with the 04/15/07
format.

ARRGGH!
 
B

Bob Greenblatt

I have a bunch of dates like 04/15/07.

They sort like text looking putting all the months in order when I
need the date in order. I needs to look at year first, then month,
then day.

Command 1 shows that the column is defined as a date with the 04/15/07
format.

ARRGGH!
Click to expand...
Yes, but are you sure the values entered are really dates or is Excel
treating them as text? If it shows exactly the same in the formula bar as in
the cell, Excel thinks it's text. If it is a real date, there will be a 4
digit year in the formula bar. Try multiplying all the dates by 1 to convert
them from text to an actual date. You'll probably have to reformat them as
dates again to get them to display as you want.

Type a 1 in any unused cell. Copy it. Select the dates you want to sort.
Choose Edit-Paste special and click on multiply. Then reformat the dates.
They should now sort properly.
 
Top