Date & Time
Days until next birthday: rollover-safe annual countdown
Project a birthdate into the current calendar year with EDATE, advance 12 months if it already passed, and subtract AsOfDate.
Why EDATE beats DATE(YEAR(AsOf), MONTH(Birth), DAY(Birth))
Building this year's birthday with DATE(YEAR(B2), MONTH(A2), DAY(A2)) rolls February 29 babies onto March 1 in non-leap years. Using EDATE(BirthDate, (YEAR(AsOf) - YEAR(BirthDate)) * 12) clamps February 29 cleanly to February 28 in common years.
If this year’s anniversary is earlier than AsOfDate, calculate next year’s anniversary from the original birth date. Adding 12 months to an already clipped February 28 would incorrectly lose February 29 in the next leap year.
This example chooses February 28 for leap-day birthdays in non-leap years. That is an application policy, not a universal anniversary rule. Use date-only inputs.
| BirthDate (A) | AsOfDate (B) | Next Birthday | Days Remaining |
|---|---|---|---|
| 1992-06-20 | 2026-06-05 | 2026-06-20 | 15 |
| 1992-03-10 | 2026-03-25 | 2027-03-10 | 350 |
| 1996-02-29 | 2026-02-20 | 2026-02-28 | 8 |
Countdown formula with LET and EDATE
On June 5, 2026, a June 20 birthday is 15 days away. On March 25, 2026, a March 10 birthday has already passed, so next birthday is March 10, 2027 (365 - 15 = 350 days away).
=LET(b, EDATE(A2, (YEAR(B2) - YEAR(A2)) * 12), IF(b < B2, EDATE(A2, (YEAR(B2) - YEAR(A2) + 1) * 12), b) - B2)Exact-birthday and leap-day checks
When AsOfDate equals the birthday itself, b < B2 is FALSE and b - B2 returns 0 days remaining.
=LET(b, EDATE(A3, (YEAR(B3) - YEAR(A3)) * 12), IF(b < B3, EDATE(A3, (YEAR(B3) - YEAR(A3) + 1) * 12), b) - B3)Try the example
Use the original values or change them to test your understanding.