Formula Field, Calculate Month difference - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Fred KampfFred Kampf 

Formula Field, Calculate Month difference

Is there a way to calculate the difference between 2 dates in months....

For example... I have 2 date fields "renewal policy starts" and "renewal policy ends"

If the dates are 7/1/11 and 6/31/12.. how would I end up with a 12 ?

If I simply use the month function, I get a -1 ... ?

Any help would be greatly appreciated!!!

Thanks!!
Best Answer chosen by Moderator (salesforce.com) 
Ankit AroraAnkit Arora
Please try this and let me know if doesn't work for you :

TEXT((YEAR( End_Date__c )*12+MONTH(End_Date__c ))-
(YEAR( Start_Date__c )*12+MONTH(Start_Date__c )))

-- 
Regards
Ankit Arora | Certified Developer
    

All Answers

Ankit AroraAnkit Arora
Please try this and let me know if doesn't work for you :

TEXT((YEAR( End_Date__c )*12+MONTH(End_Date__c ))-
(YEAR( Start_Date__c )*12+MONTH(Start_Date__c )))

-- 
Regards
Ankit Arora | Certified Developer
    
This was selected as the best answer
Fred KampfFred Kampf

Thanks Ankit...that worked!!!! I just had to add a "+1 " to add one day to the end date ..

Thanks so much for helping!!!!

Fred

Ankit AroraAnkit Arora
Ohh great !!! So if it helped you, can you pelase mark it as the best answer :)

-- 
Regards
Ankit Arora | Certified Developer
    
Fred KampfFred Kampf

Thanks for your help on this.. it works if the date is the 1st of the month.. however, if the dates are on another day (the 15th for example) the formula does not work.. I get 13 months if the start date is 12/15/11 and the end date is 12/14/12.. thanks!!!

Bobby MathewBobby Mathew

This should give you the months difference irrespective of the day of the month.

MONTH(End_Date__c) + 12 - MONTH(Start_Date__c) +
12 * (YEAR(End_Date__c) - YEAR(Start_Date__c) -1 )
Banwari kevatBanwari kevat
Ankit sir,
 I need days diffrence between two days. How can we achive it in error condition in validation rule.
Janet ChadwickJanet Chadwick
This calculation works out the number of full months between dates, then adds the pro-rata days for the first month and last months (if they're not the same month).. It returns a decimal value and allows for an empty date field (assuming today's date as the end date if end date is blank), and it also allows for a membership across the year. 

I am beyond pleased with myself LOL - it took me 4 weeks to get this right.  It may have a few extra brackets, but by golly, it floats!  :)


((IF(NOT(ISBLANK( npe01__Membership_End_Date__c ))  ,(((YEAR( npe01__Membership_End_Date__c ) - YEAR(npe01__Membership_Start_Date__c ) - 1) *12) + (12 - MONTH(npe01__Membership_Start_Date__c ) +1) + MONTH(npe01__Membership_End_Date__c ))  , (((YEAR( TODAY() ) - YEAR(npe01__Membership_Start_Date__c ) - 1) *12) + (12 - MONTH(npe01__Membership_Start_Date__c ) +1) + MONTH(TODAY() )) ))  -(IF(NOT(ISBLANK( npe01__Membership_End_Date__c )),  /*IF THE GBH RES HAS LEFT */  IF( OR((MONTH(npe01__Membership_End_Date__c )-MONTH(npe01__Membership_Start_Date__c )>=2), (YEAR (npe01__Membership_End_Date__c) - YEAR (npe01__Membership_Start_Date__c ))>0) , 2, 1 ),  /*IF THE GBH RES IS CURRENT*/  IF( OR((MONTH(TODAY () )-MONTH(npe01__Membership_Start_Date__c )>=2), (YEAR (TODAY ()) - YEAR (npe01__Membership_Start_Date__c ))>0) , 2, 1 ))))  +(((IF(  MONTH( npe01__Membership_Start_Date__c ) = 12,  DATE( YEAR( npe01__Membership_Start_Date__c), 12, 31 ),  DATE( YEAR( npe01__Membership_Start_Date__c ), MONTH ( npe01__Membership_Start_Date__c ) + 1, 1 ) - 1  )- npe01__Membership_Start_Date__c)-1)/(IF(  MONTH(npe01__Membership_Start_Date__c) = 12, 31,  DAY(DATE(YEAR(npe01__Membership_Start_Date__c),MONTH(npe01__Membership_Start_Date__c)+1,1)- 1))))  /*Calculation for last month pro-rata (if last month & year are different from first month & year).*/  + ( IF( AND(YEAR( npe01__Membership_Start_Date__c ) = YEAR ( npe01__Membership_End_Date__c ),MONTH (npe01__Membership_Start_Date__c)= MONTH (npe01__Membership_End_Date__c)), 0,  IF(ISBLANK (npe01__Membership_End_Date__c), /*USE TODAY AS END DATE*/ ((DAY(TODAY()) )/  DAY(IF( MONTH( TODAY() ) = 12,  DATE( YEAR( TODAY() ), 12, 31 ),  DATE( YEAR( TODAY() ), MONTH( TODAY() ) + 1, 1) - 1))-1)  ,((DAY ( npe01__Membership_End_Date__c ))  / DAY(IF( MONTH( npe01__Membership_End_Date__c ) = 12,  DATE( YEAR( npe01__Membership_End_Date__c ), 12, 31 ),  DATE( YEAR( npe01__Membership_End_Date__c ), MONTH( npe01__Membership_End_Date__c ) + 1, 1) - 1))  ) ) ) )
 
Egil Andreu Gräs ClavelEgil Andreu Gräs Clavel
Janet that formula is boss. Absolutely nailed it, thank you! Lifesaver!
Ori HirschOri Hirsch
Janet, you rock!
Janet ChadwickJanet Chadwick
I hadn't seen these - thanks so much for the encouragement @Egil and @Ori!