How to Count Days Until the Next Birthday in Excel

Find this year’s birthday, roll it to next year once it has passed, and count the days until it — from an as-of date.

You will learn to

• Move a birthday into the current year
• Roll past birthdays into next year
• Count days until the next birthday

How it works

A birthday this year is the birth month and day with the current year: =DATE(YEAR(as_of),MONTH(B2),DAY(B2)). If that date is already behind you, the next birthday is the same day next year. Adding the comparison (this_year_birthday<as_of) to the year — TRUE counts as 1 — handles both cases in one formula.

Subtract the as-of date from the next birthday to get the days remaining. Keep the as-of date in its own cell rather than using TODAY(), so the countdown is reproducible and easy to test.

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