How to determine if more than 1 or 2 years between two dates (accounting for leap years)

jham3829

We issue certificates that expire the date they were issued + 1 year (or 2 years). For example, a 2-year certificate issued 6/1/23 will expire 6/1/25.

Occasionally, we get requests for special certificates that may be valid for less than or equal to the 1 year or 2 year term. We have a separate Smartsheet for approving these requests and we manually enter the dates in there for review. I need a formula to check if we accidently enter dates that are valid for more 1 year (or 2 years).

I have a formula that works correctly to verify it's not valid for more than the standard period - except if leap years are involved (see screenshot below).

How can I structure a formula that adjusts correctly if a leap year falls during the certificate period?

=IF([Standard Cert Length]@row = "1", IF(NETDAYS([Date of Issue]@row, [Expiration Date]@row) <= 366, "OK", "Exceeds 1 year"), IF(NETDAYS([Date of Issue]@row, [Expiration Date]@row) <= 731, "OK", "Exceeds 2 years"))

Paul Newcome

Try something like this:

=IF([Expiration Date]@row> DATE(YEAR([Date of Issue]@row) + VALUE([Standard Cert Length]@row), MONTH([Date of Issue]@row), DAY([Date of Issue]@row)), "Exceeds " + IF([Standard Cert Length]@row = "1", "1 year", "2 years"), "OK")

Paul Newcome

Try something like this:

=IF([Expiration Date]@row> DATE(YEAR([Date of Issue]@row) + VALUE([Standard Cert Length]@row), MONTH([Date of Issue]@row), DAY([Date of Issue]@row)), "Exceeds " + IF([Standard Cert Length]@row = "1", "1 year", "2 years"), "OK")

jham3829

I figured out a solution:

I added a column "Max Expiration Date" where I calculated the expiration date if it were valid for the full 1 year or 2 years.

=DATE(YEAR([Date of Issue]@row) + [Standard Cert Length]@row, MONTH([Date of Issue]@row), DAY([Date of Issue]@row))

Then in the "Date Check" column, I entered the following formula to check if it exceeded the maximum allowed term.

=IF([Expiration Date]@row > [Max Expiration Date]@row, "Exceeds Max Cert", "Date Check OK")

(I simplified the text in the "Date Check" column so it indicates it exceeds the max rather than specifying if it exceeded 1 year or 2 years)

It's amazing what a lunch break will do :)

jham3829

@Paul Newcome I like your solution even better! Saves the extra column!

Thank you so much for the prompt response!

Paul Newcome

Happy to help. 👍️