 ShowAll Questionssorted byDate Posted chiranjib rout

# i have wrote this formula but it added 1 year to conformation date field so please suggest

i have wrote this formula but it added 1 year to conformation date field so plz suggest
DATE(
year( DOJ__c )
+ floor((month(DOJ__c) + 6)/12) + if(and(month(DOJ__c)=12,6>=12),-1,0)
,
if( mod( month(DOJ__c) + 6, 12 ) = 0, 12 , mod( month(DOJ__c) + 6, 12 ))
,
min(
day(DOJ__c),
case(
max( mod( month(DOJ__c) + 6, 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,if(mod((year(DOJ__c)
+ floor((month(DOJ__c) + 6)/12) + if(and(month(DOJ__c)=12,6>=12),-1,0)),4)=0,29,28),
31
)
)
) Best Answer chosen by chiranjib rout Tavva Sai Krishna
Hi Rout,
try with this formula . Syntax of this formula is correct but I havent check with the result.
NOTE:  Here replace the timeline__c with the DOJ__c.
```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), 1, 31,

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

3,31,4,30,5,31,6,31,7,30,8,31,9,30,10,31,11,30,12,31,0)
, DAY(Timeline__c))  )
```

Explanation of this formula:
```DATE(
//Year value is increased by 1 if the month of the "timeline" date is more than 7.

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

//Month value is subtracted with 12 if it is greater than 12 when Added with 6.

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

//Day value is dynamic as the months may have 30,31 or even 29 if it is leapYear. so we need to check the same as //per the date day value.If the date is 31,29,28 , then it uses the "case" formula

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), 1, 31,
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),

3,31,4,30,5,31,6,31,7,30,8,31,9,30,10,31,11,30,12,31,0)
, DAY(Timeline__c))

)```

Let me know if you face any queries/face any issues.

Thanks and Regards,
Sai Krishna Tavva. Tavva Sai Krishna
Hi Rout,

Thanks and Regards,
Sai Krishna Tavva. chiranjib rout
hii tavva, 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. Tavva Sai Krishna
Hi Rout,
try with this formula . Syntax of this formula is correct but I havent check with the result.
NOTE:  Here replace the timeline__c with the DOJ__c.
```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), 1, 31,

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

3,31,4,30,5,31,6,31,7,30,8,31,9,30,10,31,11,30,12,31,0)
, DAY(Timeline__c))  )
```

Explanation of this formula:
```DATE(
//Year value is increased by 1 if the month of the "timeline" date is more than 7.

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

//Month value is subtracted with 12 if it is greater than 12 when Added with 6.

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

//Day value is dynamic as the months may have 30,31 or even 29 if it is leapYear. so we need to check the same as //per the date day value.If the date is 31,29,28 , then it uses the "case" formula

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), 1, 31,
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),

3,31,4,30,5,31,6,31,7,30,8,31,9,30,10,31,11,30,12,31,0)
, DAY(Timeline__c))

)```

Let me know if you face any queries/face any issues.

Thanks and Regards,
Sai Krishna Tavva.
This was selected as the best answer Tavva Sai Krishna
Hi Rout,

Thanks and Regards,
Sai Krishna Tavva. chiranjib rout
Hi Tavva thank you very much for your help to solve this formula  ,please stay in touch chiranjib rout
hii Tavva the code is working but when i am trying date like 28 & 29 december its showing error plz suggest chiranjib rout
31 december also Tavva Sai Krishna
Hi rout,

Sorry for delay.I havent seen due to some work. I have modified the code and checked as well . Please check the answer in this post Url.
https://developer.salesforce.com/forums/ForumsMain?id=906F0000000kCv1IAE