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
chiranjib routchiranjib rout 

how do I add 6 months to a date field in a formula?

Hii friends, how do I add 6 months to a date field in a formula? There are two  Date fields date of joining(DOJ_c) and  a formulafield  (Conformation_Date_c) ,if i put any date in date of joining (DOJ_c) field then in the conformation date =(date of joining + 6 month). kindly provide the formula for  conformation Date?? For the above question i have wrote the below formula=

DATE( IF(MONTH(DOJ__c )>6, YEAR(DOJ__c ) + 1 , YEAR(DOJ__c)) ,
IF( MONTH(DOJ__c)+6 > 12, (MONTH(DOJ__c)+6)-12, MONTH(DOJ__c)+6) ,
IF(OR(DAY(DOJ__c) = 31,DAY(DOJ__c) = 29,DAY(DOJ__c) = 28),
CASE(IF( MONTH(DOJ__c)+6 > 12, (MONTH(DOJ__c)+6)-12, MONTH(DOJ__c)+6), 1, 31,

2, IF(OR(MOD(IF(MONTH(DOJ__c )>6, YEAR(DOJ__c ) + 1 , YEAR(DOJ__c )),400)=0,AND(MOD(IF(MONTH(DOJ__c )>6, YEAR(DOJ__c ) + 1 , YEAR(DOJ__c )),4)=0,MOD(IF(MONTH(DOJ__c )>6, YEAR(DOJ__c ) + 1 , YEAR(DOJ__c )),100)<>0)),
29, 28),
3,31,4,30,5,31,6,31,7,30,8,31,9,30,10,31,11,30,12,31,0)
, DAY(DOJ__c)) )


The  Formula code is working but when i am trying  the date like 28,29,31 December its showing error plz suggest
Dhanya NDhanya N
Hi Chiranjib,

Please check this formula: 
IF( MONTH(Date__c )>6, 
	IF(DAY(Date__c ) = 31 && (MONTH(Date__c ) =1 || MONTH(Date__c ) = 3 || MONTH(Date__c ) = 5 || MONTH(Date__c ) = 8 || MONTH(Date__c ) = 10 || MONTH(Date__c ) =12 ), 
		DATE( YEAR(Date__c )+1 , MOD (MONTH(Date__c )+7,12) , 1 ) , 
		DATE( YEAR(Date__c )+1 , MOD (MONTH(Date__c )+6,12) , DAY(Date__c ) ) 
	),	
	IF(DAY(Date__c ) = 31 && (MONTH(Date__c ) =1 || MONTH(Date__c ) = 3 || MONTH(Date__c ) = 5 || MONTH(Date__c ) = 8 || MONTH(Date__c ) = 10 || MONTH(Date__c ) =12 ), 
		DATE( YEAR(Date__c ) , MOD (MONTH(Date__c)+7, 12) , 1 ) , 
		DATE( YEAR(Date__c ) , MONTH(Date__c )+6 , DAY(Date__c ) ) 
	) 
)

Thanks,
Dhanya
Jerome RussJerome Russ
Hello!
Salesforce addresses this scenario in their examples:
https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm#adding-day-to-date
Tavva Sai KrishnaTavva Sai Krishna
Hi rout,

I have modified the formula. please check below: 
DATE(   IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c ))  ,

  IF( MONTH(Timeline__c)+6 > 12, (MONTH(Timeline__c)+6)-12, MONTH(Timeline__c)+6) ,

  IF(OR(DAY(Timeline__c) = 31,DAY(Timeline__c) = 29,DAY(Timeline__c) = 28), 

  CASE(
  IF( MONTH(Timeline__c)+6 > 12, (MONTH(Timeline__c)+6)-12, MONTH(Timeline__c)+6), 

  2,  IF(OR(MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),400)=0,AND(MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),4)=0,MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),100)<>0)),
  29, 28),

 4,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),6,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),9,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),11,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),DAY(Timeline__c)) 
, DAY(Timeline__c))  )

Also I checked and it works fine. Let me know if you face any issues. 

Thanks and Regards,
Sai Krishna Tavva.
Aditya KamdiAditya Kamdi
Hi Krishna it works just fine except 30th august 2016 as the first date thanks its been great help. I will try and look into it 
 
Aditya KamdiAditya Kamdi

Hopefully this should work. Cheers Mate!!

DATE(   IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c ))  ,

  IF( MONTH(Timeline__c)+6 > 12, (MONTH(Timeline__c)+6)-12, MONTH(Timeline__c)+6) ,

  IF(OR(DAY(Timeline__c) = 31,DAY(Timeline__c) = 30,DAY(Timeline__c) = 29,DAY(Timeline__c) = 28), 

  CASE(
  IF( MONTH(Timeline__c)+6 > 12, (MONTH(Timeline__c)+6)-12, MONTH(Timeline__c)+6), 

  2,  IF(OR(MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),400)=0,AND(MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),4)=0,MOD(IF(MONTH(Timeline__c )>6,  YEAR(Timeline__c ) + 1 , YEAR(Timeline__c )),100)<>0)),
  29, 28),

 4,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),6,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),9,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),11,IF(DAY(Timeline__c)==31,30,DAY(Timeline__c)),DAY(Timeline__c)) 
, DAY(Timeline__c))  )
 

 
Kacper Augustyniak 18Kacper Augustyniak 18
Hey, guys. What's the compiled size of your formulas?

Because I think I have leap-year-proof formula for adding months (doesn't work with negative months count) but compile size is pretty big - 2,257 characters.
  
DATE( 
/* set year */
YEAR(Start_Date__c) + FLOOR ( ( (MONTH(Start_Date__c)+(Months_To_Add__c) ) / 12)  ), 
/* set month */
MOD((MONTH(Start_Date__c) + (Months_To_Add__c) - 1), 12)+1, 
/* set day to the first day of the month */
1 )
/* add days */
/* check if the day is not bigger than last day in month */
+ MIN(
DAY(Start_Date__c),
DAY( 
DATE ( YEAR(Start_Date__c) + FLOOR ( ( (MONTH(Start_Date__c)+(Months_To_Add__c) ) / 12)  ) , MOD((MONTH(Start_Date__c) + (Months_To_Add__c) ), 12)+1 , 1) -1)
) 
- 1