Autopopulate Date based on contract renewal terms

Question

Hello,

I am looking for a formula that would help with calculating a date based on dates and criteria in two other columns.

Here is the scenario:

I have a contract with an expiration date. This contract is auto renewed for a certain number of years (3, 5, 7). I would like to create a column that contains a formula that calculates the expiration date after the auto renewal period is over.

For example, I have a contract that expires on 12/31/2020. It is auto renewed for 3 years. So the final expiration date is 12/31/2023. Is there a way I can get the date column to take the expiration date and add 3 years to it?

Thank you!

Kelly Moore · Accepted Answer

Hi Ashleigh

In this example, I called your columns Renewal Years (your 3,5, or 7), Expiration Date and Final Expiration. This formula would go in the Final Expiration column and make sure that column is formatted as a Date column.

When adding/subtracting years or months to a date, we have to use the formula below.

Try this

=DATE(YEAR([Expiration Date]@row) + [Renewal Years]@row, MONTH([Expiration Date]@row), DAY([Expiration Date]@row))

Be sure to change the formula to match your actual column names.

Hope this helps