Dates & Time
Days Until Next Annual Birthday or Contract Renewal (Ignoring Original Year)
Calculate exact calendar days from a reference date until the next annual birthday, SaaS renewal, or calibration date—wrapping cleanly across December 31 into next calendar year.
Count down to annual renewals and birthdays across year-end boundaries!
Subtracting two raw dates includes every elapsed year since hire or birth, whereas DATEDIF(Birth, Ref, "yd") counts backward from the LAST anniversary rather than forward to the NEXT upcoming anniversary! When a SaaS renewal date is January 10 (originally signed 2021-01-10) and today is October 30, 2026, you want the forward countdown to 2027-01-10 (72 days)—while a November 15 contract (2021-11-15) hasn't occurred yet in 2026 and is only 16 days away!
Project the month and day into the current reference year via DATE(YEAR(Ref), MONTH(Orig), DAY(Orig)): if that candidate date is strictly earlier than Ref (< Ref), add +1 to the year via --(DATE(YEAR(Ref), MONTH(Orig), DAY(Orig)) < Ref), then subtract Ref! On the exact anniversary day itself, < Ref is FALSE (0), returning 0 days remaining!
| Original Hire / Sign Date (B2) | Reference Date (B3) | Next Occurrence Date | Days Remaining |
|---|---|---|---|
| 2021-11-15 (Upcoming in 2026) | 2026-10-30 | 2026-11-15 (Same Year) | 16 days |
| 2021-01-10 (Passed in 2026 -> +1 yr) | 2026-10-30 | 2027-01-10 (Wraps to 2027!) | 72 days |
| 2019-10-30 (Anniversary Today!) | 2026-10-30 | 2026-10-30 | 0 days ("Due Today!") |
Days Remaining Until Next Annual Anniversary (Year-Wrap Safe)
Projects MONTH(B2) and DAY(B2) into YEAR(B3), bumps the year by +1 if already passed this year, and subtracts B3.
=DATE(YEAR(B3) + --(DATE(YEAR(B3), MONTH(B2), DAY(B2)) < B3), MONTH(B2), DAY(B2)) - B3Formatted Next Upcoming Annual Renewal Date (ISO yyyy-mm-dd)
Displays the exact calendar date of the upcoming annual milestone.
=TEXT(DATE(YEAR(B3) + --(DATE(YEAR(B3), MONTH(B2), DAY(B2)) < B3), MONTH(B2), DAY(B2)), "yyyy-mm-dd")Try the example
Use the original values or change them to test your understanding.