Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to sort by day and month while ignoring year?
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. |
#2
|
|||
|
|||
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 "Robert Judge" wrote: 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. |
#3
|
|||
|
|||
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) -- Don Guillett SalesAid Software "Robert Judge" wrote in message ... 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. |
#4
|
|||
|
|||
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? "Peo Sjoblom" wrote: 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 "Robert Judge" wrote: 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. |
#5
|
|||
|
|||
Robert
John Walkenbach has a free downloadable add-in for working with pre-1900 dates. http://j-walk.com/ss/excel/usertips/tip028.htm Gord Dibben Excel MVP On Thu, 23 Dec 2004 14:27:03 -0800, Robert Judge wrote: 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? "Peo Sjoblom" wrote: 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 "Robert Judge" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|