Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
chiranjib routchiranjib 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 KrishnaTavva 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. 

All Answers

Tavva Sai KrishnaTavva Sai Krishna
Hi Rout,

what is you explain your requirement. I didnt get you. could you please elaborate your question.

Thanks and Regards,
Sai Krishna Tavva.
chiranjib routchiranjib 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 KrishnaTavva 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 KrishnaTavva Sai Krishna
Hi Rout,

Hope you are not facing any issues. if any please provide the details about it here, else please mark the answer as best answer as it might helpful to others.

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

Please mark the answer as best Answer if it works.

Thanks and Regards,
Sai Krishna Tavva.