Formula field - Compiled formula is too big to execute

Question

You probably have been facing such issues in the past, the Formula field I wrote to calculate the Time remaining to complete cases seems to hit a size limitation. The problem arises when I try to calculate the Time remaining in business day. My formula grows exponentially and I could not find a way to make my code simpler, I believe it is impossible to save variables before hands in a formula field.

(IF(ISBLANK(Start_date_for_Remaining_Formula__c),NULL,
     CASE( MOD( DATEVALUE( Start_date_for_Remaining_Formula__c ) - DATE( 1900, 1, 7 ), 7 ),
     0, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 1 + Time_to_discount__c, 
     4, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c, 
     5, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c,
    6, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c,
    DATEVALUE( Start_date_for_Remaining_Formula__c ) + Time_to_discount__c
    )
)
- DATEVALUE(NOW()))*24

As a work around I tried to create a Field update with the new formula but then I was only able to trigger it with a workflow rule when a record is being edited, which does not comply with the requirements I got.

Answer 1

Formulas effectively include the formula's text they reference literally in the underlying SQL with some overhead. You need to simplify your formulas, or leverage workflow rules or Apex Code to minimize the impact on formula space, which is limited to 5,000 characters compiled.

As an aside, just like in algebra, you can pull common factors out of the case statement, leaving you with:

(IF(ISBLANK(Start_date_for_Remaining_Formula__c),NULL,
DATEVALUE( Start_date_for_Remaining_Formula__c ) +
CASE( MOD( DATEVALUE( Start_date_for_Remaining_Formula__c ) - DATE( 1900, 1, 7 ), 7 ),
0, 1, 4, 2, 5, 2, 6, 2, 0)
+ Time_to_Discount__c)
- DATEVALUE(NOW()))*24

Edit: Explaination

To arrive at the solution, I first pulled out the DATEVALUE(Start_Date_for_Remaining_Formula__c):

(IF(ISBLANK(Start_date_for_Remaining_Formula__c),NULL, DATEVALUE(Start_date_for_remaining_formula__c) + CASE( MOD( DATEVALUE( Start_date_for_Remaining_Formula__c ) - DATE( 1900, 1, 7 ), 7 ), 0, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 1 + Time_to_discount__c, 4, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c, 5, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c, 6, DATEVALUE( Start_date_for_Remaining_Formula__c ) + 2 + Time_to_discount__c, DATEVALUE( Start_date_for_Remaining_Formula__c ) + Time_to_discount__c ) ) - DATEVALUE(NOW()))*24

Next, I noted that I could remove Time_to_discount__c, except that the "default" parameter would then have no value, so I substituted "0 + Time_to_discount__c" into that section:

(IF(ISBLANK(Start_date_for_Remaining_Formula__c),NULL, DATEVALUE(Start_date_for_remaining_formula__c) + CASE( MOD( DATEVALUE( Start_date_for_Remaining_Formula__c ) - DATE( 1900, 1, 7 ), 7 ), 0, 1 + Time_to_discount__c, 4, 2 + Time_to_discount__c, 5, 2 + Time_to_discount__c, 6, 2 + Time_to_discount__c, 0 + Time_to_discount__c ) ) - DATEVALUE(NOW()))*24

Finally, you can move the "+ Time_to_discount__c" to the area after the case statement:

(IF(ISBLANK(Start_date_for_Remaining_Formula__c),NULL, DATEVALUE(Start_date_for_remaining_formula__c) + CASE( MOD( DATEVALUE( Start_date_for_Remaining_Formula__c ) - DATE( 1900, 1, 7 ), 7 ), 0, 1 + Time_to_discount__c, 4, 2 + Time_to_discount__c, 5, 2 + Time_to_discount__c, 6, 2 + Time_to_discount__c, 0 + Time_to_discount__c ) + Time_to_discount__c ) - DATEVALUE(NOW()))*24

This leads to the final form I originally provided.