Or use a different formula in a single helper column:

=text(a1,"mmdd")
or
=(month(a1)*100)+day(a1)



Mike H wrote:

OOPS,

That wont work, you need 2 helper columns

Column B formula

=day(a1)

Column C formula

=Month(A1)

Now drag these 2 column down and then do a sort first on column C ascending
and a secondry sort on column B ascending. Hide both the helper columns.

Mike

"Mike H" wrote:

Pete,

When you enter 3/2 or 25/4 despite not entering a year Excel recognises it
as a date and add the current year but you wont see it because of your
formatting and that's why you sort has gone pear shaped since the start of
the new year.

If you want to sort by day and month only then you need to create another
column to do this called a helper column.

Lets say your dates are in column A starting in A1. Put this in B1 and drag
down as far as there are data in columnA

=DAY(A1)&MONTH(A1)

Select both columns and sort on this newly created column and your dates
will sort as you want them.

You can then hide or delete the helper column but if you frequently add
dates then perhaps hidden is the best option.

Mike

"pete5440" wrote:

I have a worksheet with one column being dates in the format of 3/2, 4/25,
12/10, etc. As I added dates I could always sort from lowest (1/1) to highest
(12/31). Now I can't seem to sort any dates that have been added in 2009. I
do not have a year attached to these dates as they are birthdays and I need
to sort by month/day regardless of year. If I highlight A2 (A1 is my column
heading) the formula box shows 1/1/2008. I have never set up the year and
there is no formula set up as I have only ever added the month/day. This is
Excel 2007. How do I fix?


--

Dave Peterson