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.