Apex Code Development You need to sign in to do that
Don't have an account?
Carolina Calculate renewal date from close date in an opportunity
Hi,
I need to calculate the renewal date of a membership from the membership's Start date in an opp. The renewal date depends on the number of terms in months (it can be 12 months, 24 months, 3 months, etc). This is the formula I have right now:
If(Day( Start_Date__c ) >1,
Date(Year(Start_Date__c + 30* Membership_term_Months__c),
(Month(Start_Date__c + 30* Membership_term_Months__c)+2)
, 1),
Date(Year(Start_Date__c + 30* Membership_term_Months__c), (Month(Start_Date__c + 30* Membership_term_Months__c)+1),1))
This works fine when months are from January to October, when it gets to December the month should be 13, which of course is not possible. And I get this message:
"Error:A workflow or approval field update caused an error when saving this record. Contact your administrator to resolve it.: Renewal Date: value not of required type: common.formula.FormulaEvaluationException: Month or Day out of range in DATE() function"
I don't know how to keep this formula, but with the condition that if Start date is 12/2/2009, the term is 12 months, the value should be January:01/1/2011.
Thank you,
Carolina
Without getting into a really complex formula that figures out stuff like Current Month, Days in Month, Leap Year, etc. You might want to try a formula like this:
(Start_Date__c) + Membership_term_Months__c * 30.4375
In this example Start Date is a Date field, and Membership Term is a Number(2,0) field. Rather than trying to calculate the days in the month, leap year, etc. I simplified it to 1 Year = 365.25 days, 1 Month = 365.25 /12
Another thing you might want to look into is using Contracts, they have a built-in Renewal/Expiration date calculator that does exactly what you're trying to do here. And it even has an auto-reminder feature.
All Answers
Without getting into a really complex formula that figures out stuff like Current Month, Days in Month, Leap Year, etc. You might want to try a formula like this:
(Start_Date__c) + Membership_term_Months__c * 30.4375
In this example Start Date is a Date field, and Membership Term is a Number(2,0) field. Rather than trying to calculate the days in the month, leap year, etc. I simplified it to 1 Year = 365.25 days, 1 Month = 365.25 /12
Another thing you might want to look into is using Contracts, they have a built-in Renewal/Expiration date calculator that does exactly what you're trying to do here. And it even has an auto-reminder feature.