Count number of years between two dates

05/03/21
Accepted

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

  • Heather DHeather D ✭✭✭✭✭
    Accepted 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.


    Best,

    Heather

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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?

  • Hi Paul,


    no I would like to have a whole number returned .


    Thanks,

  • Heather DHeather D ✭✭✭✭✭
    Accepted 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.


    Best,

    Heather

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

  • Heather DHeather D ✭✭✭✭✭

    @LeAndre Poplus Happy to help!

Sign In or Register to comment.