Perpetual Contract Renewal Formula

ssuser01

Hello All!

I am trying to create a formula that will automatically update the end date to a contract based on a renewal timeframe. For example, Contract Start date 12.31.19, Contract end date 12.31.20 at the end of the contract, the contract will renew for 1 year perpetually on the same date. Right now we are at 11.8.24, so the next renewal will be 12.31.24. How do I create a perpetual formula?

Thank you in advance! This is a litter higher then my experience level.

Concetta

mcarlson

To create a formula that automatically updates the contract end date based on a perpetual yearly renewal, you’ll want to calculate the next renewal date based on the current date and the original contract start date. Here’s how to do it in Smartsheet.

Assuming:

• Start Date is in a column named Start Date.
• Renewal Period is 1 year (12 months).
• You want the next renewal date to update automatically.

Here’s a formula that should work to calculate the next renewal date based on today’s date:

Formula for Perpetual Contract Renewal

excelCopy code=IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Start Date]@row), DAY([Start Date]@row)),    DATE(YEAR(TODAY()), MONTH([Start Date]@row), DAY([Start Date]@row)),    DATE(YEAR(TODAY()) - 1, MONTH([Start Date]@row), DAY([Start Date]@row))) + 365

Explanation

1. Calculate Renewal Date Based on Start Date:
   • The formula uses the original start date to determine the yearly renewal.
   • DATE(YEAR(TODAY()), MONTH([Start Date]@row), DAY([Start Date]@row)) calculates the renewal date for the current year.
2. Conditional Logic:
   • If today’s date is later than the calculated renewal date, it sets the next renewal date to the same day in the current year plus 1 year (+ 365 days).
   • Otherwise, it sets it for the previous renewal year and adds a year.
3. Account for Yearly Renewal:
   • The + 365 at the end adds one year to the last renewal date, ensuring that it continues perpetually each year on the same date.

Example

For a start date of 12/31/2019, this formula calculates the next renewal date as 12/31/2024 if today’s date is 11/08/2024.

This formula can handle yearly renewals by adjusting the date based on the current date without manual input, creating a “perpetual” renewal system. Let me know if you'd like any further customization!

Paul Newcome

I would suggest this:

=DATE(YEAR(TODAY()) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Start Date]@row), DAY([Start Date]@row)), 1, 0), MONTH([Start Date]@row), DAY([Start Date]@row))

ssuser01

@Paul Newcome and @mcarlson Thank you for your help. Now, what about if the renewal 2yrs or 3 yrs. Is there a way to reference another cell that would list the renewal timeframe?