[SalesForce] how to add 6 months to a date field in a formula

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 formula field (Conformation_Date_c), if i put any date in date of joining (DOJ_c) field then in the confirmation date =(date of joining + 6 month).

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 I get an error message.

Best Answer

Adding months to a date is slightly more complicated as months vary in length and the cycle of months restart with each year. Therefore, a valid day in one month (January 31) might not be valid in another month (February 31). A simple solution is to approximate each month’s length as 365/12 days:

There is a standard solution from Salesforce (see "Adding Days, Months, and Years to a Date" section) https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm

This example formula adds two months to a given date. You can modify the conditions on this formula if you prefer different behaviors for dates at the end of the month.

DATE(
  YEAR( date ) + FLOOR( ( MONTH ( date ) + 2 - 1 ) / 12 ),
  MOD( MONTH ( date ) + 2 - 1 + 
    IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 
      2, 28,
      4, 30,
      6, 30,
      9, 30, 
      11, 30,
      31 ), 1, 0 ), 12 ) + 1,
    IF( DAY( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1,
      2, 28, 
      4, 30, 
      6, 30, 
      9, 30, 
      11, 30, 
      31 ), 
    1, DAY( date )
  )
)
Related Topic