How can I calculate the number of years between 2 dates where each calendar year must count as 1 yr?

Constance Fetter

I need to calculate the number of years between Contract Start and Contract End, where the years must be counted as whole if they are within a calendar year? This calculation is being used to create Purchase Orders that must have a single line for each calendar year, irrespective of when during the year the contract starts or ends. For example:

Start Date = 5/12/2024

End Date = 12/31/2026

I need a formula that will look at this and return "3": 1 year each for 2024, 2025 & 2026. How can I create this formula, please?

KPH

Hi @Constance Fetter

Assuming your dates are in date format columns, you can extract the year from the date using YEAR(column@row)

where column is your column name.

Once you have the years you can do the end year minus the start year plus 1 to count the span of years.

=YEAR([End Date]@row) - YEAR([Start Date]@row) + 1