Next Anniversary or Renewal Date

Excel Formulas › Date & Time

All versionsEDATEDATE

When does the next birthday, work anniversary, or annual renewal fall? Rebuild the date in the current year and bump it forward if it’s already passed.

Quick formula: for an original date in B1 (next yearly occurrence from today):
=DATE(YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))<TODAY()), MONTH(B1), DAY(B1))
Build the date with this year’s year but the original month/day; if that’s already past, add 1 to the year.

Functions used (tap for the full reference guide):

DATEEDATE

The example

Next yearly occurrence, measured from June 17, 2026.

AB
1Original dateNext occurrence
23/01/19903/1/2027
39/15/20109/15/2026
46/17/20186/17/2026

The formula

The next yearly occurrence of B1:

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))<TODAY()), MONTH(B1), DAY(B1)) // Mar 1 (past) → 3/1/2027

How it works

Reconstruct the date in the current year, then adjust:

  1. Pull the month and day from the original date with MONTH(B1) and DAY(B1).
  2. Build a candidate date in this year: DATE(YEAR(TODAY()), MONTH(B1), DAY(B1)).
  3. Compare it to TODAY(). If it’s already passed, the comparison returns TRUE (1), which adds a year; otherwise 0 keeps this year.
  4. Today itself counts as “not passed,” so an anniversary that is today returns today’s date.

Monthly renewals? Use EDATE to roll forward by months instead of a year: =EDATE(B1, CEILING((TODAY()-B1)/30, 1)) approximates the next monthly anniversary — or step a known number of months with =EDATE(B1, n).

Try it: interactive demo

Live demo

Enter an original date; see the next yearly occurrence.

Next occurrence:

Variations

Days until it

Combine with a subtraction:

=[next occurrence] - TODAY()

Which birthday / anniversary #

How many years it will mark:

=YEAR([next occurrence]) - YEAR(B1)

Monthly renewal

Step forward by months:

=EDATE(B1, n)

Pitfalls & errors

Feb 29 edge case. In a non-leap year, DATE(year,2,29) rolls to March 1. Decide whether leap-day anniversaries should fall on Feb 28 or Mar 1 and adjust if it matters.

“Today” behavior. The <TODAY() test treats today as still upcoming. Use <= if you’d rather skip to next year once the day arrives.

Format as a date. DATE returns a serial number — apply a date format to the result cell.

Practice workbook

📊
Download the free Next Anniversary or Renewal Date practice workbook
A next-anniversary calculator with days-until, anniversary-number, and monthly-renewal variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the next anniversary or renewal date in Excel?
Rebuild the date in the current year and bump it forward if passed: =DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),MONTH(B1),DAY(B1))
How do I count down to the next occurrence?
Subtract today from the result: =[next occurrence]-TODAY() gives the days remaining.
How do I handle monthly renewals?
Use EDATE to step forward by months: =EDATE(B1, n) moves the date n months ahead.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Add months to a date · Days until a date · Calculate age

Function references: DATE · EDATE