0

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))  )

Please help me to resolve this.

Thanks.

Improve this question
2
  • Based on the response from Charles, tried the formula given by him and it worked.
    – Mani
    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
    Jul 23, 2015 at 2:05

2 Answers 2

Reset to default
1 
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.

Improve this answer
4
  • thx sfdcfox! can you give me the complete formula?
    – Mani
    Jul 23, 2015 at 5:36
  • @Mani you should be able to copy and paste those two pieces together. Try it out.
    – sfdcfox
    Jul 23, 2015 at 5:54
  • Thx sfdcfox, I tried the following formula and it worked perfectly fine:
    – Mani
    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
    Jul 27, 2015 at 6:47
0

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() ) ) ) )

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .