How to find upcoming birthdays in Excel

Next birthday: this year's, or next year's if it has passed

The sheet lists names and dates of birth, with an as-of date in F1 (Thursday 5 November 2026). =DATE(YEAR($F$1),MONTH(B2),DAY(B2)) gives Maya's birthday this year, 14 March 2026. That is already in the past, so it isn't her next birthday.

The formula below adds 1 to the year when this year's birthday is before the as-of date. The comparison returns TRUE or FALSE, and Excel treats those as 1 and 0 when you add them. Maya's next birthday is 14 March 2027; Aisha's is 19 November 2026. Tom was born on 5 November, so the comparison is FALSE and his next birthday is today. Use <= instead of < if you want today's birthdays to roll to next year.

Keep the as-of date in a cell rather than writing TODAY() into every formula. You can type =TODAY() in F1 for a live list, or a fixed date to plan ahead, and every formula follows it.

Days until the birthday, and the age they turn

Subtract the as-of date from the next birthday to get the days left. Aisha has 14 days, Leo 27, Tom 0 (today) and Omar, whose birthday was three days ago, 362. Format the result as Number or General, not as a date.

To show the age they are turning, measure complete years from the date of birth to the next birthday: DATEDIF(B5, next birthday, "y") returns 25 for Leo on 2 December. A simpler equivalent is the current age plus one, =DATEDIF(B5,$F$1,"y")+1, but that is wrong for anyone whose birthday is today.

Who has a birthday in the next 30 days?

In Excel 365, work out every next birthday as an array with LET and keep the names where the gap is 30 days or less. Four people qualify: Tom (today), Aisha (14 days), Leo (27) and Nina (25), in list order.

Change 30 to 7 for a weekly reminder. To show the soonest first, swap FILTER for SORTBY with the same dates: =LET(a,$F$1,d,B2:B9,n,DATE(YEAR(a)+(DATE(YEAR(a),MONTH(d),DAY(d))<a),MONTH(d),DAY(d)),TAKE(SORTBY(A2:A9,n),3)) returns Tom, Aisha and Nina, the next three birthdays. In older Excel, put the next-birthday formula in a helper column and sort or filter on it.

Birthdays this month, and a list in calendar order

For this month's birthdays, compare the month only: =FILTER(A2:A9,MONTH(B2:B9)=MONTH($F$1)) returns Tom, Aisha, Omar and Nina for November. Omar's birthday on the 2nd is included even though it has passed.

Sorting by date of birth sorts by age, not by birthday. To list birthdays from January to December, sort by the month and day as text: TEXT(B2:B9,"mmdd") turns 14 March into 0314, which sorts correctly. The formula below gives Sofia (8 January), Zoe (29 February), Maya, Omar, Tom, Aisha, Nina and Leo (2 December). In older Excel, put =TEXT(B2,"mmdd") in a helper column and sort by it with Data > Sort.

February 29 birthdays

Zoe was born on 29 February 2008. In a year without 29 February, DATE rolls the day over: DATE(2027,2,29) returns 1 March 2027, so the next-birthday formula gives 1 March. DATEDIF agrees with that rule; it counts her as 18 on 28 February 2027 and 19 on 1 March.

Another popular formula, =EDATE(B7,12*(DATEDIF(B7,$F$1,"y")+1)), adds whole years with EDATE, and EDATE stops at the last day of the month instead, giving 28 February 2027. Neither is wrong; pick the date your family or company celebrates and use that formula. Watch out for the EDATE version on birthdays that are today: it returns next year's date, because DATEDIF already counts today's year as complete.

Highlight birthdays coming up

To color the names with a birthday in the next 7 days, use a conditional formatting rule with the same formula, written for the first row.

1. Select A2:B9.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =DATE(YEAR($F$1)+(DATE(YEAR($F$1),MONTH($B2),DAY($B2))<$F$1),MONTH($B2),DAY($B2))-$F$1<=7 with $B2 locked to the column only.
4. Click Format, choose a fill color and click OK twice.