Sorting by Birthday Anniversary

[R.S.Lynn]

On August 27 of this year, I posted a question of how, given a sheet sorted
on date of birth, to display simply the integer number of years of age. Two
approaches were offered, both of which were completely on point and gave me
a solution. ( 1.) Custom cell type yy, and (2). using the semisecret
function datedif(A1,today(),"y"). Both were completely satisfactory, and
gave the desired result. But subsequently, I wanted to prepare my aged
father, 92 years old this month, a list of his relatives and their in-laws
(they number > 100) sorted by birthday anniversary. I prepared three
columns ; mo, day, yr, by simply custom typing them mm,dd,and yy. But when
I sort on these columns, I find that they all contain simply the date of
birth data. Only the display is changed by custom typing. So I have four
columns which fundamentally contain the same data, but which are displayed
differently. When I try to sort on month, for example, I am just sorting on
date of birth, not month of birth. I want to display a sheet sorted by
birthday anniversary.

I presume that my solution lies in creating a column which contains just the
month of birth, and another which contains just the day of the month. I can
think of other possibilities, but this is salient in my mind. If I can
achieve these columns, I can go on from there.

Please suggest formulas of making, e.g., cell D2 contain just the month of
birth contained in the cell C2 --the date of birth -- and E2 contain the day
of the month of birth contained in C2. As I have noted, forcing the display
to be just the month, etc, has already been solved. I need the cell
contents to be just the month, and day of month. Or maybe there are
multiple approaches to sorting on anniversary of birth. Anything that
works.

 

[Roger Govier]

Hi

Try
=YEAR(A1)
=MONTH(A1)
=DAY(A1)

These will all return numeric values you will be able to ue in your sort.

Regards

Roger Govier

 

[R.S.Lynn]

The =day(a1) returns the day of the month, in my spreadsheet, as I would
expect from the information given by Roger Govier.
Both the =month(a1) and =year(a1) are returning incorrect values in my
spreadsheet.

Birthday Day Mo Yr
October 19, 1913 19 01 05


I assure you that the formula entry in the column headed "Mo" is =month(c2)
and likewise "Yr" is =year(c2).
The format of the "Birthday" is prototyped via format/cells/date as "March
14, 2001"
I tried changing column C to 3/14/01, with the same result. Copying the
=month(c2) and year(c2) down the respective columns returns, in each case
the erroneous "01" and "05", telling me that the error is independent of the
actual contents of column C. E.g.,

February 10, 1938 10 01 05


The day of the month is returned correctly, but month and year are not.

I am still in the process of troubleshooting this unexpected result

It is mysterious to me.