Find Upcoming Birthdays in Excel With a Formula

To find upcoming birthdays in Excel, you work out each person's next birthday, count the days until it, and then flag or list everyone whose birthday is coming soon. The tricky part is that a birth date includes the birth year, so you can't compare it with today directly. This guide gives you one formula for the next birthday, then shows how to flag, list and count upcoming birthdays. It is for HR teams, office managers, teachers and anyone who keeps a staff or member list.

All examples use this list. E1 holds the report date. In a real file, type =TODAY() there. Here it is fixed at 2 November 2026, so the results below stay the same when you read them.

Why you can't just compare the birth date with today#

A birth date such as 20 Nov 1994 is in the past, so it is always smaller than today. Subtracting it from today gives the person's age in days, not the days until their birthday. You need the same month and day in the current year, or in the next year if that date has already gone.

Excel stores dates as numbers, counting days from 1 January 1900. That is why you can subtract one date from another and get a number of days. If this is new, practise the date basics first.

The next birthday formula#

Build this year's birthday with DATE, YEAR, MONTH and DAY:

=DATE(YEAR($E$1),MONTH(B2),DAY(B2))

For Amara, this is 20 Nov 2026. For Luis, it is 7 Mar 2026, which has already passed. To move past birthdays into next year, add 1 to the year when this year's date is before the report date:

=DATE(YEAR($E$1)+(DATE(YEAR($E$1),MONTH(B2),DAY(B2))<$E$1),MONTH(B2),DAY(B2))

The comparison in brackets returns TRUE or FALSE. In a calculation, TRUE counts as 1 and FALSE as 0, so the year goes up by one only when the birthday has passed. For Luis, the result is 7 Mar 2027. For Kenji, whose birthday is today, the comparison is FALSE (today is not before today), so his next birthday is today.

If the plus-TRUE trick feels too clever, the IF version does the same: YEAR($E$1)+IF(DATE(YEAR($E$1),MONTH(B2),DAY(B2))<$E$1,1,0).

How many days until each birthday?#

Subtract the report date from the next birthday. In C2:

=DATE(YEAR($E$1)+(DATE(YEAR($E$1),MONTH(B2),DAY(B2))<$E$1),MONTH(B2),DAY(B2))-$E$1

Fill it down to C6 (Windows: Ctrl+D; Mac: ⌘D). Amara gets 18, Luis 125, Noor 119, Kenji 0 and Sofia 33. A 0 means the birthday is today. If the result shows as a date, change the cell format to General or Number.

The $ signs keep every row pointing at E1. The guide to counting days until a deadline uses the same subtraction, and you can practise days between dates in a live sheet.

How to flag birthdays in the next 30 days#

With the days in column C, a simple IF marks the ones coming up:

=IF(C2<=30,"Soon","")

Amara (18) and Kenji (0) show Soon. Sofia (33) doesn't, because 33 is more than 30. Put the 30 in a cell, such as F1, and use C2<=$F$1 so you can change the window without editing formulas.

To colour the rows instead, select A2:C6 and go to Home > Conditional Formatting > New Rule > Use a formula, then type =$C2<=30. The $ before C keeps every cell in the row looking at column C. The guide to highlighting overdue dates walks through the same kind of rule.

How to list upcoming birthdays with FILTER#

FILTER returns only the rows that meet a condition, and it updates when the data changes. To list the names with a birthday in the next 30 days:

=FILTER(A2:A6,C2:C6<=30,"None")

The result spills down as Amara and Kenji. The third argument, "None", shows when nobody matches, instead of a #CALC! error. To show the soonest first, sort the result by the days column with SORTBY:

=SORTBY(FILTER(A2:A6,C2:C6<=30),FILTER(C2:C6,C2:C6<=30))

Now Kenji (today) comes before Amara (18 days). FILTER and SORTBY need Excel 2021 or Microsoft 365. In older versions, use the filter arrows on column C instead (Data > Filter, then Number Filters > Less Than Or Equal To). See the FILTER function guide for more.

Birthdays this month and the age they will turn#

For a monthly list, compare months instead of counting days. =MONTH(B2)=MONTH($E$1) returns TRUE for birthdays in the report month. To count them, use:

=SUMPRODUCT(--(MONTH(B2:B6)=MONTH($E$1)))

On the sample data, the result is 2 (Amara and Kenji, both in November). Sofia's December birthday is only 33 days away, but it is in next month. Use this for "birthdays this month" and the days method for "the next 30 days".

To show the age each person will turn, use DATEDIF between the birth date and the next birthday:

=DATEDIF(B2,$E$1+C2,"y")

$E$1+C2 is the next birthday, so Luis shows 39 and Kenji 35. The DATEDIF function guide covers the units, and the guide to calculating age and years of service explains why DATEDIF beats dividing by 365.

Common mistakes and edge cases#

Watch for these when you build a birthday tracker:

1. Using TODAY() in every formula. It works, but one cell with =TODAY() is easier to check and lets you test another date. The TODAY function guide and the article on the TODAY function explain how it updates.
2. 29 February birthdays. DATE(2027,2,29) becomes 1 March 2027, because 2027 is not a leap year. That is why Noor shows 119 days. If you prefer 28 February, add a rule for it.
3. Birth dates stored as text. MONTH and DAY return #VALUE! on text that only looks like a date. Convert the column first.
4. Expecting the list to update on its own. TODAY() recalculates when the file opens or recalculates. A printed list is a snapshot.
5. Forgetting the year roll-over in December. A birthday on 5 January is "next year" when you check on 20 December. The next-birthday formula handles this; a simple month comparison does not.

The same "next date" idea works for anniversaries and renewals; see the expiry date tracker.

Practise it#

Birthday lists combine date basics, date maths and date conditions in one small task. Practise date basics in a live spreadsheet, then practise dates in criteria: you type each formula and ExcelReady checks it instantly. Start with Luis's next birthday in the exercise on this page.