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?

--
Ian
--

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.


IanC wrote:

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?

--
Ian
--

--

Dave Peterson

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

"IanC" wrote in message
...
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?

--
Ian
--

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

"IanC" wrote in message
...
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?

--
Ian
--