You need to sign in to do that
Don't have an account?
Formula to calculate end date similar to Contract End Date - Not working for February dates
I have the below formula that calculates an Acquiring Contract End Date based on the start date (Acquiring_Contract_Sign_Date__c) and a term (Acquiring_Contract_Term__c). It doesn't work well when the end date should be sometime in February. It gives me an error. For example, if I choose 11/30/2011 as a sign date and a term of 3 months, it should give me 2/29/2012, but it gives me an error. Any idea how to fix it? Thanks.
FORMULA:
DATE(Year(Acquiring_Contract_Sign_Date__c) +
FLOOR((MONTH(Acquiring_Contract_Sign_Date__c) +
Acquiring_Contract_Term__c - 1) / 12) ,
MOD(MONTH(Acquiring_Contract_Sign_Date__c) +
Acquiring_Contract_Term__c - 1, 12) + 1 ,
DAY(Acquiring_Contract_Sign_Date__c)) - 1
Hello,
Try the formulas from this page: http://boards.developerforce.com/t5/Formulas-Validation-Rules/How-to-make-a-Date-formula-field-which-will-add-Months/td-p/81772/page/2
Thanks,
Adrian
I tried to use the long formula and I got a formula that was too big to execute. I wish there was a simpler way to modify this so it would not throw out any errors.
I figured it out. I had to change it to a workflow because the formula was too big. It just uses a workflow update to manually enter a value into the field. Here's the formula in the workflow field update:
DATE(
Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12) ,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1,
IF(MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 12,
DAY(Contract_Sign_Date__c),
IF(DAY(Contract_Sign_Date__c)>
(DATE(
Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12) ,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 2 , 1) -
DATE(
Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12) ,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 , 1)),
(DATE(
Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12) ,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 2 , 1) -
DATE(
Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12) ,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 , 1)),
DAY(Contract_Sign_Date__c)))) -
IF(
OR(
AND(
(Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12)) / 4 =
FLOOR((Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12)) / 4),
DAY(Contract_Sign_Date__c) > 29,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 2),
AND(
(Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12)) / 4 !=
FLOOR((Year(Contract_Sign_Date__c) +
FLOOR((MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1) / 12)) / 4),
DAY(Contract_Sign_Date__c) > 28,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 2),
AND(
DAY(Contract_Sign_Date__c) > 30,
OR(
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 4,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 6,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 9,
MOD(MONTH(Contract_Sign_Date__c) +
Contract_Term__c - 1, 12) + 1 = 11)))
,0,1)