Sort by date but not year
 

A coworker maintains a list of clients' birthdays so we can send a card. She
wants to sort it by the date column so that we have all of July's birthdays
in one section, for instance. However, when she types in the month and day,
it automatically assigns the current year to the date, even though it is not
displayed. She did most of the list last year, so most of them are 2007
dates. The ones she added this year are assigned 2008, so when sorting by
date, they show up at the end, not mixed with the other July birthdays that
were entered in 2007.

How do we get a strictly month/day date with no year?

Sort by date but not year
 

You need a helper column on which to sort
Use =TEXT(MONTH(A1),"00")&" - " &TEXT(DAY(A1),"00")
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Beth Scheel" <Beth wrote in message
...
A coworker maintains a list of clients' birthdays so we can send a card.
She
wants to sort it by the date column so that we have all of July's
birthdays
in one section, for instance. However, when she types in the month and
day,
it automatically assigns the current year to the date, even though it is
not
displayed. She did most of the list last year, so most of them are 2007
dates. The ones she added this year are assigned 2008, so when sorting by
date, they show up at the end, not mixed with the other July birthdays
that
were entered in 2007.

How do we get a strictly month/day date with no year?

Sort by date but not year
 

You could create two new columns, one for the day and one for the month, the
formulae to populate these columns would be
Day (as in 1 to 31):
=day(yourDateCell)
Month(as in 1 to 12)
=month(yourDateCell)

You could then format these columns to show the month in text.

"Beth Scheel" wrote:

A coworker maintains a list of clients' birthdays so we can send a card. She
wants to sort it by the date column so that we have all of July's birthdays
in one section, for instance. However, when she types in the month and day,
it automatically assigns the current year to the date, even though it is not
displayed. She did most of the list last year, so most of them are 2007
dates. The ones she added this year are assigned 2008, so when sorting by
date, they show up at the end, not mixed with the other July birthdays that
were entered in 2007.

How do we get a strictly month/day date with no year?

Sort by date but not year
 

Other option would be to format the column as text. Then when you enter, for
example, 7/14, it will remain as 7/14. When you sort, it will give you the
option to sort anything that looks like a number as a number, so 7/14 will
still be before 10/7.

--
John C


"Beth Scheel" wrote:

A coworker maintains a list of clients' birthdays so we can send a card. She
wants to sort it by the date column so that we have all of July's birthdays
in one section, for instance. However, when she types in the month and day,
it automatically assigns the current year to the date, even though it is not
displayed. She did most of the list last year, so most of them are 2007
dates. The ones she added this year are assigned 2008, so when sorting by
date, they show up at the end, not mixed with the other July birthdays that
were entered in 2007.

How do we get a strictly month/day date with no year?

Sort by date but not year
 

Another one.

Insert a new column.
=text(a2,"mmdd")
and drag down
and sort all the data by that column.

Beth Scheel wrote:

A coworker maintains a list of clients' birthdays so we can send a card. She
wants to sort it by the date column so that we have all of July's birthdays
in one section, for instance. However, when she types in the month and day,
it automatically assigns the current year to the date, even though it is not
displayed. She did most of the list last year, so most of them are 2007
dates. The ones she added this year are assigned 2008, so when sorting by
date, they show up at the end, not mixed with the other July birthdays that
were entered in 2007.

How do we get a strictly month/day date with no year?

--

Dave Peterson