3

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?

  • 2
    It's not perfect but ABS(Date1-Date2)/30 might do the trick close enough – Sebastian Kessel Apr 7 '17 at 18:38
  • Slightly revised 'not perfect' solution if you wanted a little more precision than 30 days for the length of a month: ABS(Date1-Date2) / ( 365 / 12 ) – Mark Pond Apr 7 '17 at 19:14
  • 6
    Just a quick note that this would be trivial if you were to use Apex instead of a formula. date1Var.monthsBetween(date2Var);. I know, I know, clicks before code. However, you should weigh the costs and benefits of each approach (is not needing to write a unit test worth the extra effort that you'd need to put forth to develop an accurate formula?) – Derek F Apr 7 '17 at 19:18
6

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.

  • Mantra does not have to equal slavish devotion to the concept. :) – Adrian Larson Apr 10 '17 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.