How to list all birthdays in a given month in Excel
Excel formula
=FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays")Filter on the month number: =FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays").
- Result on the example
- a 4-row list that spills from D3 in
D3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in D3. Change any input and the result updates, or edit the formula to experiment.
How it works
MONTH(B2:B11) returns the month of every birthday at once, and FILTER keeps the rows where it equals the month in E1. The third argument is what to show when nobody has a birthday that month.
=FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays")Result{4 rows × 2 cols} "Grace", 33180, "Theo", 34294, …
A2:B1110 rows × 2 cols: "Grace", 11/3/1990, "Omar", 10/29/1984, …A range: 10 rows × 2 columns.
B2:B1110 rows × 1 col: 11/3/1990, 10/29/1984, 1/15/2001, 11/21/1993, …A range: 10 cells in one column.
MONTH(B2:B11){10 rows × 1 col} 11, 10, 1, 11, …MONTH: Month of a date, 1–12.- serial_number
B2:B1110 rows × 1 col: 11/3/1990, 10/29/1984, 1/15/2001, 11/21/1993, …
- serial_number
E111The value in E1.
MONTH(B2:B11)=E1{10 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays"){4 rows × 2 cols} "Grace", 33180, "Theo", 34294, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:B1110 rows × 2 cols: "Grace", 11/3/1990, "Omar", 10/29/1984, … - condition1
MONTH(B2:B11)=E1{10 rows × 1 col} TRUE, FALSE, FALSE, TRUE, … - condition2, ...
"No birthdays"
- range
In Google Sheets
Google Sheets needs a different formula: =FILTER(A2:B11,MONTH(B2:B11)=E1). 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(FILTER(A2:B11,MONTH(B2:B11)=E1),DAY(FILTER(B2:B11,MONTH(B2:B11)=E1)),1)Excel only | spills 4 × 2Different result | Also sorts the list by day of the month. Sorted by day: Grace, Nina, Theo, Zoe. |
Common mistakes
- Google Sheets FILTER has no "if empty" argument; wrap it in IFERROR instead:
=IFERROR(FILTER(A2:B11,MONTH(B2:B11)=E1),"No birthdays"). - Blank birthday cells count as month 1 and show up in January. Add a test:
(MONTH(B2:B11)=E1)*(B2:B11<>"").
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.
List all birthdays in a given month
D3, list the names and birthdays of everyone born in the month number in E1.Questions
How do I list this month's birthdays automatically?
Use MONTH(TODAY()) in place of E1.
How do I list the birthdays in the next 30 days?
Calculate the days until each next birthday in a helper column and filter on it.
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.