function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BuellBuell 

Next Renewal Date Field... Help!

We have contracts (1 to 5 years) which auto-renew unless the customer cancels.  For these renewals we keep the original opportunity record in SFDC to track.  I have created a 'renewal date field' populated off of what deal term is selected (1-5) and the closed date but the problem is with customers that have already cycled through 2 or more terms of their contract, the renewal date is now useless.

I want to have a field that calculates what the next renewal date is.  Anyone have any suggestions?

Here is what i have so far, and I think I'd be able to finish it off if it weren't for the character limitations.

Term_to_Number__c: converts 'deal term' dropdown field to it's equivalent number.

A field which determines if the current year is a renewal year:
Code:
IF((YEAR(TODAY()) = YEAR(CloseDate )),
   'No', 
   IF ((MOD(((YEAR(TODAY()) - YEAR(CloseDate) ) / Term_to_Number__c), FLOOR(((YEAR(TODAY()) - YEAR(CloseDate) ) / Term_to_Number__c )))) = 0,
    'Yes',
    'No'
   )
)

 A field which determines how many deal terms have passed:
Code:
FLOOR ((( YEAR( TODAY() ) - YEAR( CloseDate ) ) / Term_to_Number__c ))

 The next renewal year: (The problem with this one is if this year is a renewal year, it adds on one more term.  Tried to account for it with an IF() pulling on the renewal year formula above to determine whether or not to subtract a term from the total, but the formula came out too big.)
Code:
YEAR ( CloseDate ) + ( Term_to_Number__c * (Terms_Completed__c + 1))

 So that is where I am.  Any ideas?

Thanks in advance.

UPDATE:

changed the field which determines the terms completed to:
Code:
FLOOR(( TODAY() - CloseDate ) / ( Term_to_Number__c * 365 ))

 This takes care of the issue with the "Next Renewal Year" field where it was adding another term.




Message Edited by Buell on 09-26-2008 02:35 PM

Message Edited by Buell on 09-26-2008 02:51 PM
Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell
OK I found the solution.  One formula field only and a new field replacing the drop down 'deal term' field with a number field called 'contract length' (in years).  Hope this helps someone out.

Code:
DATE(
YEAR ( CloseDate ) + (  Contract_Length__c  * (FLOOR(( TODAY() - CloseDate ) / (  Contract_Length__c  * 365 ))  + 1)),
MONTH(CloseDate),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

 
The IF() at the end of the formula accounts for leap years.


Message Edited by Buell on 09-26-2008 03:38 PM

All Answers

BuellBuell
This would be the solution but it compiles to 10,563 characters:

Code:
DATE(
YEAR ( CloseDate ) + ( Term_to_Number__c * (Terms_Completed__c  + 1)),
MONTH(CloseDate),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

 



Message Edited by Buell on 09-26-2008 02:55 PM
BuellBuell
OK I found the solution.  One formula field only and a new field replacing the drop down 'deal term' field with a number field called 'contract length' (in years).  Hope this helps someone out.

Code:
DATE(
YEAR ( CloseDate ) + (  Contract_Length__c  * (FLOOR(( TODAY() - CloseDate ) / (  Contract_Length__c  * 365 ))  + 1)),
MONTH(CloseDate),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

 
The IF() at the end of the formula accounts for leap years.


Message Edited by Buell on 09-26-2008 03:38 PM
This was selected as the best answer
nycsalesopsnycsalesops
I just tried this formula and it worked great for our 12 month agreements but we also have some agreements that are 3 or 6 months that are also auto-renewable. It doesn't seem to be able to calculate the next renewal date for those. I have also tried adding a pickval statement because not all of our agreements are auto-renewable and I was hoping that the formula could capture that as well. So far I have failed miserably capturing both those variables. Does anyone have any suggestions?
BuellBuell
I have a few other formulas for terms measured in months.  The problem is that they become so large that you have to make some sacrifices.  If you just need the next renewal date to show up on the record then I have an s-control that will get you an exact date, however if you need it for reporting purposes then you have to sacrifice some accuracy, meaning you will always get the first of the month on your renewal date or whatever day you choose.  Let me know what your needs are and I'll point you in the right direction.