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
Jennifer HarrisJennifer Harris 

Formula for Notification on a Contract Renewal

I am having trouble figuring out a formula used within a workflow that will notify a person of a renewal on a contract. The tricky thing is that we have a field called Contract Exp Date (date field). This is the end of a contract. We have another field called Renewal Options (picklist: values = 1 year, 2 years, 3 years, 4 years, 5 years, other). Even though a contract is officially up at the time of the Contract Exp Date, if the Renewal Options are set to 1 year, the rep needs a notification a year into the contract. (Fyi- we do not use the Contract object... these are custom fields). For example: If we have a Contract Exp Date of 6/30/2020, Renewal Option of 1 year, and today is 1/26/15, the end user will need a notification that the contract is up for renewal 90 days before 6/30/2015. Then, the next year, they will need a notification 90 days before 6/30/2016 and so on. Is there a way to do this?

I possibly thought about maybe creating a field to say what the next Renewal Date would be based on the Contract Exp Date and Renewal Options, and then use the Renewal Date to create the workflow, but I am faced with the same difficulty of figuring out the formula.
Best Answer chosen by Jennifer Harris
Boom dev9xBoom dev9x
Jennifer,

Here is the sample of the formula for the Renewal date:
  
CASE(
Renewal_Options__c, 
'1 Year',  DATEVALUE(TEXT(YEAR(TODAY())+1)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
'2 Years',  DATEVALUE(TEXT(YEAR(TODAY())+2)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
'3 Years', DATEVALUE(TEXT(YEAR(TODAY())+3)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
null
)

Hope this helps.

Boom

All Answers

Boom dev9xBoom dev9x
Jennifer,

Here is the sample of the formula for the Renewal date:
  
CASE(
Renewal_Options__c, 
'1 Year',  DATEVALUE(TEXT(YEAR(TODAY())+1)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
'2 Years',  DATEVALUE(TEXT(YEAR(TODAY())+2)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
'3 Years', DATEVALUE(TEXT(YEAR(TODAY())+3)&"-"&TEXT(MONTH(Contract_EXP_Date__c))&"-"&TEXT(DAY(Contract_EXP_Date__c))),
null
)

Hope this helps.

Boom
This was selected as the best answer
Jennifer HarrisJennifer Harris
This formula worked like a charm! Thank you so much.
Boom dev9xBoom dev9x
Jennifer,

could you you please mark the answer as "Solved"?

thank you!

boom