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

Answers

  • mcarlson
    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!

    Murphy Carlson

    DigitalRadius, Smartsheet Platinum Partner

    Schedule a Meeting

    mcarlson@digitalradius.com

  • Paul Newcome
    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))

  • @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
    ssuser01 ✭✭
    edited 11/21/24

    @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
    Paul Newcome ✭✭✭✭✭✭
    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))

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

  • Paul Newcome
    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.

  • oh ok- I understand. Thank you!