why does date sort as text?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dzelnio - 20 Jul 2007 16:17 GMT

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!

Reply to this Message

Bob Greenblatt - 20 Jul 2007 16:57 GMT

On 7/20/07 11:17 AM, in article
1184944649.077448.280820@q75g2000hsh.googlegroups.com, "dzelnio"

> I have a bunch of dates like 04/15/07.
>
[quoted text clipped - 6 lines]
>
> ARRGGH!

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.

Signature

Bob Greenblatt [MVP], Macintosh
bobgreenblattATmsnDOTcom

Reply to this Message

Mac Forum / Applications / Excel / July 2007

why does date sort as text?