How do I account for leap years when adding 1 or 3 years to a date?

Pam Grant

I have a database of clients on programs with expiry dates based on qualification date. Some programs expire in 3 years and some expire in 1 year. I was adding either 364 or 1094 to the date. Leap year seems to be throwing a wrench into this. Any ideas?

Here is my code. I have a few other conditions in the calculation but the one I'm struggling with is adding 1 or 3 years to the qualification date and not losing a day because of the leap year.

=IF(AND([Meet 17025 2017]@row = "no", [APPLICABLECSA PROGRAM IDENTIFIER]@row = "WMTC", [QUALIFICATION DATE]@row < DATE(2019, 11, 30)), [QUALIFICATION DATE]@row + 364, IF(AND([Meet 17025 2017]@row = "no", [APPLICABLECSA PROGRAM IDENTIFIER]@row = "TMPC", [QUALIFICATION DATE]@row < DATE(2019, 11, 30)), [QUALIFICATION DATE]@row + 364, IF(AND([Meet 17025 2017]@row = "no", [APPLICABLECSA PROGRAM IDENTIFIER]@row = "FC", [QUALIFICATION DATE]@row < DATE(2019, 11, 30)), [QUALIFICATION DATE]@row + 364, IF(AND([Meet 17025 2017]@row = "no", [APPLICABLECSA PROGRAM IDENTIFIER]@row = "CTF Stage 1", [QUALIFICATION DATE]@row < DATE(2019, 11, 30)), [QUALIFICATION DATE]@row + 364, IF(AND([Meet 17025 2017]@row = "no", [APPLICABLECSA PROGRAM IDENTIFIER]@row = "CTF Stage 2", [QUALIFICATION DATE]@row < DATE(2019, 11, 30)), [QUALIFICATION DATE]@row + 364, IF(AND([Meet 17025 2017]@row = "no", [QUALIFICATION DATE]@row > DATE(2017, 11, 30)), DATE(2020, 11, 30), IF(AND([Meet 17025 2017]@row = "no", [QUALIFICATION DATE]@row > DATE(2019, 11, 30)), DATE(2020, 11, 30), IF([Registered CTF TYPE “LSV”]@row = 1, [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "WMTC", [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "TMPC", [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "CTF Stage 1", [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "CTF Stage 2", [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "APT", [QUALIFICATION DATE]@row + 364, IF([APPLICABLECSA PROGRAM IDENTIFIER]@row = "FC", [QUALIFICATION DATE]@row + 364, [QUALIFICATION DATE]@row + 1094))))))))))))))

Paul Newcome

Instead of

[QUALIFICATION DATE]@row + 364

try

DATE(YEAR([QUALIFICATION DATE]@row) + 1, MONTH([QUALIFICATION DATE]@row), DAY([QUALIFICATION DATE]@row))

Just change the 1 to a 3 for three years.

Paul Newcome

Instead of

[QUALIFICATION DATE]@row + 364

try

DATE(YEAR([QUALIFICATION DATE]@row) + 1, MONTH([QUALIFICATION DATE]@row), DAY([QUALIFICATION DATE]@row))

Just change the 1 to a 3 for three years.

Pam Grant

Hi @Paul Newcome

Thank you! This is almost perfect, however, I need to get the expiry date which is 1 day prior. Eg. Jun 5, 2020 for 1 year would expire on June 4, 2021. So I added a -1 at the end of your formula:

DATE(YEAR([QUALIFICATION DATE]@row) + 1, MONTH([QUALIFICATION DATE]@row), DAY([QUALIFICATION DATE]@row))-1

That worked! You're the best!

I want to make sure I understand your formula: you are telling Smartsheet to

• return a DATE
• Whatever year is in QUALIFICATION DATE plus 1 year,
• Whatever month is in QUALIFICATION DATE,
• Whatever day is in QUALIFICATION DATE

In my above example, it would return an expiry of DATE(2021,06,05)-1, which is June 4, 2021

Paul Newcome

Your understanding is absolutely correct! 👍️