Sorting dates, ignoring year

[IanC]

I have a speadsheet of birthdays which I would like to change to be
birthdates. At the moment, everyone's birthdays are specified on one column
in the format dd/mm/2002 so that I can sort the worksheet to keep the dtaes
in order throughout the year.

Is there an easy way to sort a column of dates but ignore the year? Or do I
need to split the dates into multiple columns?

 

[Dave Peterson]

You could split the dates into separate columns:

=month(a1)
=day(a1)
and
=year(a1)
(you wouldn't use the year column)

But I'd insert a helper column with formulas like:

=text(a1,"mmdd")
(and drag down)
And sort the entire range by this column.

 

[Fred Smith]

Yes, add a helper column which has just the month and day. For example,
their birthday this year would be:
=date(year(today()),month(a1),day(a1))

Now sort on this.

Regards,
Fred

 

[Ashish Mathur]

Hi,

Try this

1. Select the range of dates and press Ctrl+1 (for Format cells). Go to
Number > Category > Custom > Type > mmmm
2. The above step will show months instead of dates
3. Now select the range again and go to
Excel 2003 - Data > Sort > Options > First key sort order > January,
February, March etc.
Excel 2007 - Data > Sort > Order > Custom List > January, February,
March etc.
4. Step 3 will get the dates sorted by month
5. Select the range of months and press Ctrl+1 (for Format cells). Go to
Number > Category > Custom > Type > dd/mm/yyyy

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com