How to sort birthdays by month and day in Excel
Excel formula
=SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1)Sort by the month, then by the day, and ignore the year: =SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1).
- Result on the example
- a 8-row list that spills from D2 in
D2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
SORTBY sorts a table by arrays you calculate on the fly: first by the MONTH of each birthday, then by the DAY within the month. The year never enters the sort, so Mia, born in 2010, sorts between people born decades earlier.
Google Sheets has no SORTBY, but its SORT accepts calculated sort columns in the same way.
=SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1)Result{8 rows × 2 cols} "Lucas", 32159, "Noah", 31081, …
A2:B98 rows × 2 cols: "Ava", 7/14/1991, "Noah", 2/3/1985, …A range: 8 rows × 2 columns.
B2:B98 rows × 1 col: 7/14/1991, 2/3/1985, 11/29/2002, 2/21/1979, …A range: 8 cells in one column.
MONTH(B2:B9){8 rows × 1 col} 7, 2, 11, 2, …MONTH: Month of a date, 1–12.- serial_number
B2:B98 rows × 1 col: 7/14/1991, 2/3/1985, 11/29/2002, 2/21/1979, …
- serial_number
DAY(B2:B9){8 rows × 1 col} 14, 3, 29, 21, …DAY: Day of the month, 1–31.- serial_number
B2:B98 rows × 1 col: 7/14/1991, 2/3/1985, 11/29/2002, 2/21/1979, …
- serial_number
SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1){8 rows × 2 cols} "Lucas", 32159, "Noah", 31081, …SORTBY: Sorts a range by other ranges. Spills.- array
A2:B98 rows × 2 cols: "Ava", 7/14/1991, "Noah", 2/3/1985, … - by_array1
MONTH(B2:B9){8 rows × 1 col} 7, 2, 11, 2, … - sort_order1
1 - by_array2
DAY(B2:B9){8 rows × 1 col} 14, 3, 29, 21, … - sort_order2
1
- array
In Google Sheets
Google Sheets needs a different formula: =SORT(A2:B9,MONTH(B2:B9),TRUE,DAY(B2:B9),TRUE). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SORTBY(A2:B9,TEXT(B2:B9,"mmdd"),1)Excel only | spills 8 × 2 | Sorts by a single month-day code like 0117. |
Common mistakes
- Sorting the birthday column directly orders by year first, which sorts by age, not by the calendar.
- Older Excel has no SORTBY. Add a helper column
=TEXT(B2,"mmdd")and sort the table on it. - The result spills into D2:E9. Anything typed in that area blocks it with #SPILL!.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sort birthdays by month and day
D2, return the names and birthdays sorted by month and then day, ignoring the year.Questions
How do I show the next birthdays first?
Sort by the days until each next birthday instead of by month.
How do I list only one month's birthdays?
Filter first: =FILTER(A2:B9,MONTH(B2:B9)=11).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.