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

Paul Newcome

Your formula is subtracting one from the year. Try this instead:

=DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)) + ([Auto Renewal Period]@row * IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)), 2, 1))

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?

ssuser01

@mcarlson

I have run into a little issue with the formula. It isn't adding the renewal timeframe:

Formula:

=IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)), DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)), DATE(YEAR(TODAY()) - 1, MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row))) + [Auto Renewal Period]@row

Paul Newcome

Your formula is subtracting one from the year. Try this instead:

=DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)) + ([Auto Renewal Period]@row * IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)), 2, 1))

ssuser01

Hi @Paul Newcome Thank you for your help. Could you explain the * and 2,1 in the formula

Paul Newcome

Basically we are adding the [Auto Renewal Period] to the starting date (which is the original month and day of the current year).

That gives us the renewal date for the current year. The problem is if the date within the current year is already past, then we need to go to the next one.

So we use an IF statement to basically multiply the [Auto Renewal Period] by 1 or 2 to either go for current or next based on today's date.

ssuser01

oh ok- I understand. Thank you!

ssuser01

HI @Paul Newcome,

Happy Friday! - I am working through my process and I found another situation where the date was not changing to the correct date. Please take a look:

Now the difference from the prior example is that this contract will end on 12/10/26 and will need to be renegotiated, also there isn't an auto renewal period attached.

Formula as it stands:

=DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)) + ([Auto Renewal Period]@row * IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Agreement End Date]@row), DAY([Agreement End Date]@row)), 2, 1))