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
Jennifer.SchnellJennifer.Schnell 

What is the correct formula for this?

I am using a formula field "Term" to find the number of MONTHS in a contract.  I want to return even partial months.
Examples:
Start Date: 3/15/2017
End Date:  3/31/2018
Result: 12.5

Start Date: 3/15/2017
End Date:  12/15/2017
Result:  9

What I have now is:
IF(
(Contract2_End_Date__c - Contract2_Start_Date__c) / 365 * 12 < 1, 1, (Contract2_End_Date__c - Contract2_Start_Date__c) / 365 * 12
)

How do I assure accuracey when there is leap year involved?
 
Lokesh KumarLokesh Kumar
Hi Jennifer,
Hope you are doing great.

This is what we use. Our requirements were to include any partial month (even 1 day) as a full month for the count so if the start date is the last date of the month and the end date is the first day of the next, it is 2 months. YMMV. 
IF(NOT(ISBLANK(End_Date__c)) && NOT(ISBLANK(Start_date__c))
     ,(((YEAR(End_Date__c ) - YEAR(Start_Date__c ) - 1) *12) + (12 - MONTH(Start_Date__c ) +1) + MONTH(End_Date__c ))
     , null
)

OR--------------
  
(((YEAR(End_Date__c ) - YEAR(Start_Date__c ) - 1) *12) + (12 - MONTH(Start_Date__c ) +1) + MONTH(End_Date__c )
- 1+ IF(DAY(End_Date__c ) > DAY(Start_Date__c ),1,0))

Please mark it Best if this solved your problem.

Happy to help you
 
Lokesh KumarLokesh Kumar
You can try this one also .
  
IF( 
(testdate2__c - testdate1__c) / 365 * 12 < 1, 1, (testdate2__c - testdate1__c) / 365 * 12
)