Requirement:

In my current project, based on Commencement Date of Resource, I need to send out email notification to several departments(HR, Payroll etc) and I need to do it if Resource completes 6, 12, 24 and 36 months. Basically, I had to add 6, 12,24 and 36 months to Commencement Date field and based on that date, I need to trigger Workflow notification(Time Trigger).

Analysis:

Salesforce help site https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm has a formula and it is as follows, but unfortunately it throws compilation error(Compiled formula is too big to execute (5,503 characters). Maximum size is 5,000 characters) when tried:

IF(
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 2,
IF(
DAY( Commencement_Date__c ) > 28,
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( Commencement_Date__c ) + 6 ) / 12 ), 3, 1 ),
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( Commencement_Date__c ) + 6 ) / 12 ), 2, DAY( Commencement_Date__c ) )
),
IF(
OR(
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 4,
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 6,
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 9,
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 11
),
IF(
DAY( Commencement_Date__c ) > 30,
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( Commencement_Date__c ) + 6 ) / 12 ),
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) + 1, 1 ),
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( Commencement_Date__c ) + 6 ) / 12 ),
MOD( MONTH( Commencement_Date__c ) + 6, 12), DAY( Commencement_Date__c ) )
),
IF(
MOD( MONTH( Commencement_Date__c ) + 6, 12 ) = 0,
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( date ) + 6 ) / 12 ) – 1, 12, DAY( Commencement_Date__c ) ),
DATE( YEAR( Commencement_Date__c ) + FLOOR( ( MONTH( Commencement_Date__c ) + 6 ) / 12 ),
MOD( MONTH( Commencement_Date__c ) + 6, 12), DAY( Commencement_Date__c ) )
)
)
)

Solution:

Thanks to Chris (http://www.salesforceweek.ly/author/chris) and the link http://www.salesforceweek.ly/2015/01/how-to-formula-add-months-to-date.html gives a good post about adding months to a Date.

As per the above link, I tried the following formula which adds n months(6,12, 24 and 36 month) works perfectly fine(including leap years) where n means :

DATE (  /*YEAR*/  

YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + n - 1)/12), 

 /*MONTH*/  
 
CASE(MOD(MONTH(Commencement_Date__c) + n, 12 ), 0, 12, MOD(MONTH(Commencement_Date__c)+ n, 12 )),  

/*DAY*/  

MIN(DAY(Commencement_Date__c),  CASE(MOD(MONTH(Commencement_Date__c) + n,12), 9, 30, 4, 30, 6, 30, 11, 30, 2,  

/* return max days for February dependent on if end date is leap year */  

IF(MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + n)/12), 400) = 0 || (MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + n)/12), 4) = 0 && MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + n)/12), 100) <> 0  ), 29,28), 31))  )

Please let me know your comments as always.