Auto Renew every X years

Hi,
I'm trying to calculate the dates that contracts will auto renew. One column has the effective date, the next column shows how many years it will auto renew for, and I want the expiration column to show when the auto renew is up.
So if the contract is effective 9/1/22 and is auto renewable for two years, the expiration column should show 9/1/24. But when we reach 9/1/24 the expiration column should change to two years after that, 9/1/26 and two years after that 9/1/28 etc....
I don't want ANY manual work.
Thank you.
Best Answer
-
This should fix that:
=DATE(YEAR([Original Date Column Name]@row) + CEILING(YEAR(TODAY()) - YEAR([Original Date Column Name]@row) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row)), 1, 0), [Number Of Years Column Name]@row), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row))
Answers
-
Give this a try:
=DATE(YEAR([Original Date Column Name]@row) + CEILING(YEAR(TODAY()) - YEAR([Original Date Column Name]@row) + 1, [Number Of Years Column Name]@row), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row))
-
It almost works! However, if the auto renew date is still suppose to take place form today until the end of the year, it's showing the next one, not the auto renew that's suppose to happen this year.
So if a contract has an effective date of 10/1/18, with one year terms, the formula is showing that the next auto renew date is 10/1/23 instead of 10/1/22. See below.
Any ideas?
-
This should fix that:
=DATE(YEAR([Original Date Column Name]@row) + CEILING(YEAR(TODAY()) - YEAR([Original Date Column Name]@row) + IF(TODAY() > DATE(YEAR(TODAY()), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row)), 1, 0), [Number Of Years Column Name]@row), MONTH([Original Date Column Name]@row), DAY([Original Date Column Name]@row))
-
Thanks @Paul Newcome that did the trick!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!