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)



  • KPH
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!