Get Difference Between 2 Dates in Months Without Exceeding 5000 Characters

Question

I am trying to determine the variance (in months) between an actual date and a planned date.

The actual date can be either before or after the planned date, and this is what is making this difficult.

I have the following formula to determine the difference in months

TEXT( 
IF(NOT(ISBLANK(Reference_Data_Item__c)), 
(((YEAR(Stage_6_Complete__c) - YEAR(Stage_6_Complete_DB_Forecast_Date__c) - 1) * 12) 
+ 
(12 - MONTH(Stage_6_Complete_DB_Forecast_Date__c) + 1) 
+ 
MONTH(Stage_6_Complete__c) 
- 1), 0)) & " Months"

This formula works fine for actual dates that end up being passed the planned date, but if the actual date ends before the planned date the variance has an extra 12 months added to it.

I would normally simply add a check to see which date comes first, but this is pushing me over the 5000 character limit salesforce imposes.

Does anyone have any ideas how I can get the difference while staying under the 5000 limit?

Answer 1

Because my comment appears to have been well-received (and for sake of having an answer on this question), I'll throw in my two cents.

I don't think using a formula field is appropriate here. I know that the Salesforce mantra (one of them, at least) is "Clicks before code", but, as you've found out, working with dates gets complicated very quickly.

Apex has the Date class (relevant documentation), and one of the methods that the class provides is Date.monthsBetween().

Based on the formula you've provided, you're just interested in the difference in month numbers between your two dates. Date.monthsBetween() does exactly that.

For the cost of writing a (hopefully) simple unit test, and performing a deployment, you could take care of this with ease as part of a before update trigger. Some example code:

trigger myTrigger on Opportunity(before update){
    // Best practice would be to put the below code in a class outside of the trigger,
    //   but this'll suffice for purposes of demonstration.
    for(Opportunity opp : trigger.new){
        // If you want the absolute difference, just wrap the left-hand side of the 
        //   assignment below in Math.abs()
        opp.Date_Diff__c = opp.Stage_6_Complete__c.monthsBetween(opp.Stage_6_Complete_DB_Forecast_Date__c);
    }
}

Only 2 lines of code (I didn't include the check for reference_item__c, which would add an extra line to your actual code) is very good. You might want to perform a few additional checks to make sure that both dates aren't null, but that's about it.