Find the next annual date for an event

Turn November 4, 2026 and March 4, 2027 into November 4, 2027.

What you'll make

In Excel: November 4, 2026 and March 4, 2027 becomes November 4, 2027.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1YEAR(B8)The year of the as-of date.2027
2IF( … )Adds 1 year if this year's date has passed.0
3MONTH(A8)The month of the event.11
4DAY(A8)The day of the event.4
5DATE( … )Puts the year, month and day together as a date.November 4, 2027

Read it left to right. Only the event's month and day are used, not its year.

=DATE(YEAR(B8)+IF(TEXT(B8,"mmdd")>TEXT(A8,"mmdd"),1,0),MONTH(A8),DAY(A8))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own dates. Paste event dates and as-of dates into the green columns of the Template tab. The next dates fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Leave out February 29 events. In other years they move to March 1.

Microsoft's help: DATE function