Date & Time
Sort birthdays by month and day (ignoring birth year)
Order team birthdays or annual renewals from January 1 to December 31 regardless of birth year using a numeric MMDD key and SORTBY.
Why standard date sorting scrambles annual birthday calendars
Sorting raw birthdates ascending orders people from oldest to youngest (1979 before 2001) rather than placing February 1 ahead of July 20.
Computing an integer MMDD key = MONTH(Birthdate) * 100 + DAY(Birthdate) maps Jan 5 to 105, Feb 1 to 201, Feb 29 to 229, and Nov 15 to 1115—preserving exact calendar order across leap years and every locale!
| Name (A) | Birthdate (B) | MMDD Key (C) | Calendar Rank |
|---|---|---|---|
| Maya | 1988-11-15 | 1115 | 4th (Nov 15) |
| Leo | 2001-02-04 | 204 | 2nd (Feb 4) |
| Zoe | 1994-02-01 | 201 | 1st (Feb 1) |
| Sam | 1979-07-20 | 720 | 3rd (Jul 20) |
MMDD sort key and SORTBY calendar order formulas
SORTBY(A2:A5, C2:C5, 1) spills names in clean January-to-December calendar order without altering your HR table.
=TEXTJOIN(", ", TRUE, SORTBY(A2:A5, C2:C5, 1))Numeric MMDD helper key
Multiplying MONTH by 100 guarantees days 01–31 never collide across adjacent months.
=MONTH(B2) * 100 + DAY(B2)Inputs and interpretation
Use valid numeric dates; text-date parsing varies by locale. This sorts January through December, not next birthdays from today. Define tie order for identical birthdays. February 29 remains between February 28 and March 1; observing it in a non-leap year is a separate policy. TEXTJOIN displays the sorted names in one cell.
Try the example
Use the original values or change them to test your understanding.