Adding months to a Date field using formula in SFDC

I tried the same formula which is given in https://developer.salesforce.com/forums/ForumsMain?id=906F00000008vsfIAA with my fields substituted(added 36 months in my case) and I got an error saying Mod expected Number but received Date.Here it is:

``````DATE (

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

/*MONTH*/

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

MIN(DAY(Commencement_Date__c),

CASE(MOD(MONTH(Commencement_Date__c) + 36,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) + 36)/12), 400) = 0 || (MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 4) = 0 && MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 100) <> 0  ), 29,28), 31))  )
``````

Thanks.

• Based on the response from Charles, tried the formula given by him and it worked.
– Mani
Commented Jul 23, 2015 at 2:04
• It is:DATE( year( Commencement_Date__c ) + floor((month(Commencement_Date__c) + 36)/12) + if(and(month(Commencement_Date__c)=12,36>=12),-1,0) , if( mod( month(Commencement_Date__c) + 36, 12 ) = 0, 12 , mod( month(Commencement_Date__c) + 36, 12 )) , min( day(Commencement_Date__c), case( max( mod( month(Commencement_Date__c) + 36, 12 ) , 1), 9,30, 4,30, 6,30, 11,30, 2,if(mod((year(Commencement_Date__c) + floor((month(Commencement_Date__c) + 36)/12) + if(and(month(Commencement_Date__c)=12,36>=12),-1,0)),4)=0,29,28), 31 ) ) )
– Mani
Commented Jul 23, 2015 at 2:05

``````MOD(MONTH(Commencement_Date__c)+ Commencement_Date__c, 12 ))
``````

A number plus a date is a new date, offset by the number added.

You don't need a formula that complex. That was only for adding months (less than a year).

Just do this:

``````Commencement_Date__c+1095
``````

If you really must worry about that leap day, you can add this:

`````` -if(and(floor(year( Commencement_Date__c)/4)<>floor((year(Commencement_Date__c)+3)/4),or(and(month( Commencement_Date__c)=1,day( Commencement_Date__c)=29),month( Commencement_Date__c)>1)),1,0)
``````

In other words, if a leap year is within the next three years, subtract a day so the final date falls on the same day.

• thx sfdcfox! can you give me the complete formula?
– Mani
Commented Jul 23, 2015 at 5:36
• @Mani you should be able to copy and paste those two pieces together. Try it out.
– sfdcfox
Commented Jul 23, 2015 at 5:54
• Thx sfdcfox, I tried the following formula and it worked perfectly fine:
– Mani
Commented Jul 27, 2015 at 6:44
• DATE (YEAR( Commencement_Date__c ) + FLOOR((MONTH(Commencement_Date__c) + 36 - 1)/12), CASE(MOD(MONTH(Commencement_Date__c) + 36, 12 ), 0, 12, MOD(MONTH(Commencement_Date__c)+ 36, 12 )), MIN(DAY(Commencement_Date__c), CASE(MOD(MONTH(Commencement_Date__c) + 36,12), 9, 30, 4, 30, 6, 30, 11, 30, 2, IF(MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 400) = 0 || (MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 4) = 0 && MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 100) <> 0 ), 29,28), 31)) )
– Mani
Commented Jul 27, 2015 at 6:47

Below formula adds 1 month for todays date and returns the value. (except when it is leap year it will return 28 feb even though there are 29 days in feb, so 1 month from 29 jan is 28 feb even in leap year )

``````DATE( IF( MONTH( TODAY() )+ 1>12, YEAR( TODAY() )+1 ,YEAR( TODAY() ) ),
``````

IF( MONTH( TODAY() )+ 1>12 , (MONTH( TODAY() )+ 1) -12 , MONTH( TODAY() )+ 1) , IF( (DAY(TODAY()) >=30 && MONTH( TODAY() )= 12) , 28, IF( DAY(TODAY())= 31 && (MONTH( TODAY() )= 7) , 30, DAY( TODAY() ) ) ) )