ShowAll Questionssorted byDate Posted
chiranjib 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 N
Hi Chiranjib,

```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 Russ
Hello!
Salesforce addresses this scenario in their examples:
Tavva 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.
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

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