Formula to Calculate Anniversary Years From a Date

An anniversary year is the count of complete twelve-month cycles that have elapsed between a start date and a reference date. The concept appears everywhere from wedding milestones to employee service recognition, lease renewal dates, and insurance policy terms. The formula behind it is straightforward, but two details trip people up repeatedly: whether the anniversary falls on the same calendar day each year when leap years are involved, and what “complete year” means when you need a number and not just a whole count.

What Does the Anniversary Year Formula Actually Count?

An anniversary year formula counts complete, elapsed 12-month cycles from a fixed start date to a target date. It does not count partial years, and it does not round up. A couple married on 15 March 2018 who check their anniversary count on 14 March 2025 have completed 6 full years, not 7, because the seventh anniversary has not yet arrived.

That “not yet arrived” test is the key concept. The formula always asks whether the anniversary month and day have passed in the current calendar year before crediting that year. Two people hired on the same date in the same year always share identical anniversary counts, regardless of which day they check. The formula is binary at the boundary: the day before adds nothing, the anniversary day itself adds one.

This matters in real applications. Employee service awards often use a “years of completed service” count for pension vesting and bonus eligibility. Lease agreements use it to trigger rent review clauses. Insurance policies use it to define coverage renewal windows. A miscalculation of one year in any of these contexts carries meaningful practical consequences.

How Do You Calculate Full Anniversary Years Manually?

The manual method works in three steps and requires no software.

Step 1: Subtract the start year from the current year.

Step 2: Check whether the anniversary month and day have already occurred in the current year.

• If yes: the result from Step 1 is the full anniversary year count.
• If no: subtract 1 from the result.

Step 3: Round nothing. Keep the whole number only.

Worked Example A: Anniversary Has Passed This Year

Start date: 10 June 2015. Reference date: 22 August 2025.

• Step 1: 2025 minus 2015 = 10
• Step 2: 10 June has already passed in 2025 (22 August is after 10 June). No subtraction needed.
• Result: 10 full anniversary years (rounded to the nearest whole year, which is the whole number itself)

Worked Example B: Anniversary Has Not Yet Passed This Year

Start date: 20 November 2018. Reference date: 3 September 2025.

• Step 1: 2025 minus 2018 = 7
• Step 2: 20 November has not yet passed in 2025 (3 September is before 20 November). Subtract 1.
• 7 minus 1 = 6
• Result: 6 full anniversary years

Use the anniversary calculator to run any pair of dates without manual arithmetic.

How Do You Calculate Anniversary Years in Excel?

Excel offers two functions for this task, each suited to a different output need.

For whole completed years (the standard anniversary count):

=DATEDIF(A1, B1, "y")

Place the start date in A1 and the end date (or TODAY()) in B1. The “y” argument returns only fully completed years, discarding any partial year. This is the correct function for service anniversaries, contract milestones, and age calculations.

For a decimal year (fractional precision):

=YEARFRAC(A1, B1)

This returns a decimal such as 6.75, meaning 6 full years and approximately three-quarters of a seventh year have elapsed. Use this when you need fractional precision, for example prorating a benefit that scales with exact tenure.

To calculate from today automatically:

=DATEDIF(A1, TODAY(), "y")

This updates every time the spreadsheet recalculates, so the count stays current without manual editing.

According to Microsoft’s official Excel documentation, DATEDIF is a legacy function inherited from Lotus 1-2-3 compatibility. It does not appear in the autocomplete formula suggestions but is fully functional in all current Excel and Google Sheets versions.

Task	Correct Function	Returns
Count full years (anniversary)	=DATEDIF(A1, B1, "y")	Whole number
Count full years from today	=DATEDIF(A1, TODAY(), "y")	Whole number, auto-updating
Decimal year fraction	=YEARFRAC(A1, B1)	Decimal (e.g. 6.75)
Full years, months, days	=DATEDIF(A1,B1,"y") &"y " & DATEDIF(A1,B1,"ym") &"m"	Text label

How Does the Leap Year Rule Affect Anniversary Dates?

Leap years affect one specific edge case: a start date of 29 February. This date only exists in a leap year. In non-leap years, the anniversary falls on either 28 February or 1 March depending on the organisation’s policy and jurisdiction.

For most purposes (employee service, personal anniversaries), 28 February is the accepted anniversary date in non-leap years. The formula logic does not change; only the reference day shifts. No additional calculation is needed; the DATEDIF function handles this automatically by using 28 February in non-leap years.

For all other start dates, leap years have no effect on the anniversary year count. The formula remains the same.

Sources

• Microsoft support: DATEDIF function, DATEDIF syntax, “y” argument behaviour, and legacy function disclosure.
• ExcelJet: Calculate years between dates, YEARFRAC usage and decimal year output explanation.

Calculate exact years, months, and days between any two dates with the time and date calculators hub.