function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
aaronbedwell.ax1665aaronbedwell.ax1665 

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

aaronbedwell.ax1665aaronbedwell.ax1665

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.

aaronbedwell.ax1665aaronbedwell.ax1665

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)