function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Manibalan SampathkumarManibalan Sampathkumar 

Adding months to a Date field using formula

Hi,

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. The formuala I tried:

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.
Best Answer chosen by Manibalan Sampathkumar
Manibalan SampathkumarManibalan Sampathkumar
Based on the response from Charles, I tried the following formula and it worked:

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