Auto Renew every X years



I'm trying to calculate the dates that contracts will auto renew. One column has the effective date, the next column shows how many years it will auto renew for, and I want the expiration column to show when the auto renew is up.

So if the contract is effective 9/1/22 and is auto renewable for two years, the expiration column should show 9/1/24. But when we reach 9/1/24 the expiration column should change to two years after that, 9/1/26 and two years after that 9/1/28 etc....

I don't want ANY manual work.

Thank you.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    This should fix that:

    =DATE(YEAR([Original Date Column Name]@row) + CEILING(YEAR(TODAY()) - YEAR([Original Date Column Name]@row) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row)), 1, 0), [Number Of Years Column Name]@row), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!