Perpetual Contract Renewal Formula
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
Best Answer
-
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))
Answers
-
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 namedStart 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
- 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.
- 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.
- 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 (
- 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.
- The
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!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
-
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))
-
@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?
-
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
-
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))
-
Hi @Paul Newcome Thank you for your help. Could you explain the * and 2,1 in the formula
-
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.
-
oh ok- I understand. Thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives