How to sort by day and month while ignoring year?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.
 
You could use 2 help columns with formulas, assume the dates are in A and in
B put

=MONTH(A2)

in C

=DAY(A2)


copy down and then select all 3 columns, sort by first B and then C

or use just one help column (B) and an arbitrary date like this year

=DATE(2004,MONTH(A2),DAY(A2))

or

=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

copy down and sort by the help column


Regards,

Peo Sjoblom
 
An additional column with this and then sort both using this as key.

=TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")
or
=MONTH(A1) &"/" & DAY(A1)
 
OK, thanks. This is a list of ancestor's birthdays, and some are in the
nineteenth century. Your formula worked for dates with years in the
twentieth century. However, when the year in in the nineteenth century, the
formula returns an error message of "#VALUE!" How can I correct that?
 
Back
Top