Find the most recent anniversary

Turn November 4, 2001 and January 15, 2026 into November 4, 2025.

What you'll make

In Excel: November 4, 2001 and January 15, 2026 becomes November 4, 2025.

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.2026
2IF( … )Takes away 1 year if this year's date is still ahead.1
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, 2025

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 latest 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