How to sort dates correctly?

**Mark53** · 01-10-2011, 06:26 PM

Hi, Excel-ers,

I think this should be pretty simple, but I can't figure it out...
Using Excel 2007, I made a column of Dates by:
=MONTH(D1)&"/"&DAY(D1)
and it looks like:
1/11
1/2
1/23
1/25
1/5
1/7
11/1
and the problem is... THAT's how they come out as Sorted !

How can I get them sorted correctly?

Thanks.

Mark53

**DonkeyOte** · 01-10-2011, 06:29 PM

As you are listing them they are Text Strings rather than Dates.

To use Text strings you would need to Format the Month & Day with leading zeroes:

Please Login or Register  to view this content.

If you want them as dates use

Please Login or Register  to view this content.

**DonkeyOte** · 01-10-2011, 06:42 PM

I should have added, the other alternative would be to sort via the formulae themselves.

Dates are numbers so assuming you have no other numbers stored in Col D (other than the Dates):

Please Login or Register  to view this content.

**Mark53** · 01-10-2011, 07:35 PM

Thanks for your fast reply, Don.

Excel says that the format of the cells is Date, not Number or Text or General.
Regardless, I tried the Custom format idea...
It sorted them including the Year (which is included in the column D dates).
Many different years, so that didn't work.

I thought I wanted them to remain as Dates, but apparently not.
I used your =TEXT(D1,"mm/dd") idea, and it does exactly as I want.

Terrific ! Thanks !