Formula to calculate future annual date after initial maturity date has expired

MMiles

I need to create a new column date in the future on an annual basis when the initial maturity date has expired.

Below are the 3 columns I use to determine the initial contract end date based on effective date and the term. After the initial term the contracts then renew on an annual basis. Every contract period end date (either the end of initial term or the end of the next renewal period), agreements need to be reviewed 60 days prior to the renewal date. I currently have a workflow notification set up to send a reminder 60 days before the "Contract End Date" but if the date is in the past my trigger will not work. So I am trying to come up with a formula for another date column that will take the contract end date into consideration and if that initial period is in the past come up with a formula date that will be the new renewal date year after year.

Basically I need a formula that looks at the Contract End Date, if in the past then uses the month and date to calculate the new annual renewal date that is in the future. Since I have different years in which contracts have expired I can't simply write the formula to add 12 months, the first line is a perfect example the new renewal period needs to calculate to be 4/11/23, the second line 7/19/23, line 5 would be 9/28/25 and so on...

Any help is greatly appreciated.

Hollie Green

Try this formula

=[BLDate/Effective Date]@row+([Term]@row/12*365)

MMiles

Thanks for trying however this formula did not work, it returns the value I have for Contract End Date