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
Tejaswi YellepeddiTejaswi Yellepeddi 

Contract Renewal Date Custom Field

Hi,
I have fields like this for uploading a Contract. I want to add a Custom field "Current Renewal Term". This field should look at the "Renewal Term" field and if it says Annually, it should display a value to reflect the current term.
Adding a Custom Field for Renewal Date

For example, in this case, the Contract Start Date is 6/25/2012 and Contract Term is 12 months, therefore the Contract End Date is auto-populated to 6/24/2013. But since the Renewal Term is "Annually", the custom field that I want to create "Current Renewal Term" should display 6/24/2016.
How can I make that happen?
It is a little urgent and all help is really appreciated.
Thank you,
Tejaswi
Best Answer chosen by Tejaswi Yellepeddi
KaranrajKaranraj
If its based on current date then you don't have to update the formula. Use 'Today()' instead of 'contractate' in the formula and add 1 from the current year. Here is the updated formula 
IF(
RenewalTerm = 'Annually',
IF(
AND(Month( Today())=2,Day(ContractEndDate)=29),
DATE(YEAR( Today()) + 1, Month(Today()),Day(Today())-1),
DATE(YEAR( Today()) + 1, Month(Today()),Day(Today()))
),
NULL
)


 

All Answers

KaranrajKaranraj
Tejaswi - Correct me if my understanding is wrong. If the Renewal term is Annually then you need to add 2 years from the contract end date and populate in the custom field. Here is the formula  
IF(
RenewalTerm = 'Annually',
IF(
AND(Month( ContractEndDate )=2,Day(ContractEndDate)=29),
DATE(YEAR( ContractEndDate ) + 2, Month( ContractEndDate ),Day( ContractEndDate )-1),
DATE(YEAR( ContractEndDate ) + 2, Month( ContractEndDate ),Day( ContractEndDate ))
),
NULL
)

If the Renewal term is Annually then it will add two years from the Contract end date else it will display null in the field.
Tejaswi YellepeddiTejaswi Yellepeddi
Thank you for your response Karanraj.
Although, I have a small doubt, now, since the End date is 2013, we will have to add 3 years, since June has already passed for 2015 and the Contract has been renewed till the next year.
Also, since the Contract End Date is auto-populated and has a fixed value of 2013, what happens next year when the contract is renewed again, would we have to go in and change the formula to 4?
Please help.
Thank you,
Tejaswi
 
KaranrajKaranraj
Tejaswi - What is the logic to update Contract renewed? How the contract renewal update will happen in the system? Your contract renwal date is based on the Contract end date or the current date?
Tejaswi YellepeddiTejaswi Yellepeddi
Karanraj - I guess Current date. There are some contracts that were signed in 2009 for 12 months but are renewed anually. So current date should be "renewed for 2016"
KaranrajKaranraj
If its based on current date then you don't have to update the formula. Use 'Today()' instead of 'contractate' in the formula and add 1 from the current year. Here is the updated formula 
IF(
RenewalTerm = 'Annually',
IF(
AND(Month( Today())=2,Day(ContractEndDate)=29),
DATE(YEAR( Today()) + 1, Month(Today()),Day(Today())-1),
DATE(YEAR( Today()) + 1, Month(Today()),Day(Today()))
),
NULL
)


 
This was selected as the best answer
Tejaswi YellepeddiTejaswi Yellepeddi
Thank you so much!