Count number of years between two dates

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,

Find more posts tagged with

Accepted answers

Heather Duff

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

All comments

Paul 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?

LeAndre P

Hi Paul,

no I would like to have a whole number returned .

Thanks,

Heather Duff

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

LeAndre P

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

Heather Duff

@LeAndre Poplus Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.