function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
kstites01kstites01 

Formula to calculate partial months on contracts

Hi,

I am trying to calculate the number of months between my contract start and end dates on an opportunity.  I would like it to calculate partial months though and it doesn't.  Can someone please help? 
IF(DAY(Contract_Start_Date__c) = 1, (((YEAR( Contract_End_Date__c ) - YEAR( Contract_Start_Date__c )) *12) + (MONTH( Contract_End_Date__c ) - MONTH( Contract_Start_Date__c ) ) +1), ((YEAR( Contract_End_Date__c) - YEAR( Contract_Start_Date__c)) *12) + (MONTH( Contract_End_Date__c) - MONTH( Contract_Start_Date__c) ) )

 
Dan ErvinDan Ervin
I used CASE() to get an exact calculation for days of the month. The Total Months field should be a Formula(Number,2) field.

Try this: 
((YEAR(Contract_End_Date__c) - YEAR(Contract_Start_Date__c)) * 12) + 
(MONTH(Contract_End_Date__c) - MONTH(Contract_Start_Date__c)) + 
((DAY(Contract_End_Date__c) /
 CASE(MONTH(Contract_End_Date__c),
  1, 31,
  2, 28,
  3, 31, 
  4, 30, 
  5, 31, 
  6, 30,
  7, 31,
  8, 31,
  9, 30,
  10, 31,
  11, 30,
  12, 31,
  30
 )
) -
DAY(Contract_Start_Date__c) /
 CASE(MONTH(Contract_End_Date__c),
  1, 31,
  2, 28,
  3, 31, 
  4, 30, 
  5, 31, 
  6, 30,
  7, 31,
  8, 31,
  9, 30,
  10, 31,
  11, 30,
  12, 31,
  30
 )
)