# Count number of years between two dates

Options

Hello

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

• ✭✭✭✭✭✭
Answer ✓
Options

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.

Best,

Heather

## Answers

• ✭✭✭✭✭✭
Options

Would you want something that outputs a decimal such as

Effective = 1 April 2019

Expiration = 1October 2020

Years = 1.5

Or would you want the above to output 2?

• Options

Hi Paul,

no I would like to have a whole number returned .

Thanks,

• ✭✭✭✭✭✭
Answer ✓
Options

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.

Best,

Heather

• Options

Thanks Heather... that did it!! :)

• ✭✭✭✭✭✭
Options

@LeAndre Poplus Happy to help!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!