How can I calculate the number of years between 2 dates where each calendar year must count as 1 yr?
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?
Constance Fetter (she/her/elle)
Answers

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
Help Article Resources
Categories
Check out the Formula Handbook template!