Count number of years between two dates



I have read a few posts about calculating values between dates but I'm still having an issue. I am trying to calculate the number of contractual years between a Warranty Effective Date and the Warranty Expiration Date. The string I have is noted below but it’s obviously not working. Any thoughts? 

=IF(AND(MONTH([Warranty Effective Date]1) >= MONTH([Warranty Expiration Date]1()), DAY([Warranty Effective Date]1) > DAY([Warranty Expiration Date]1())), YEAR([Warranty Effective Date]1()) - 1 - YEAR([Warranty Effective Date]1), YEAR([Warranty Effective Date]1()) - YEAR([Warranty Effective Date]1)

Thanks in advance,

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @LeAndre Poplus ,

    I think I understand what you're doing. If you really just want the years between the two numbers (as long as the expiration date is after the effective date), you should be able to do this:

    =if([Warranty Expiration Date]@row>[Warranty Effective Date]@row,YEAR([Warranty Expiration Date]@row)-YEAR([Warranty Effective Date]@row),"")

    This translates to - if the expiration date is after the effective date, calculate the number of years between the expiration and effective dates. If not, display blank.

    Hope this helps! Let me know if it works.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!