2

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.

| improve this question | |
  • 1
    Can Start_Date_For_Remaining_Formula__c be set by workflow instead of being a formula? – sfdcfox Jul 30 '15 at 16:35
3

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.

| improve this answer | |
  • - Thanks a lot, I will try to update Start_date_for_Remaining_Formula__c by worklow rather than formula. - Also I just realized that my Time_to_Discount__c can vary from few hours to several days, making things more complex I am afraid? - The simplification you make looks great, but it will take me some time to understand how you pulled that 0, 1, 4, 2, 5, 2, 6, 2, 0! – Maxime Petit Jul 30 '15 at 16:48
  • @MaximePetit I added the precise logic I used to arrive at the new formula. – sfdcfox Jul 30 '15 at 17:27
  • Great explaination thank you, the additional 0 and shortcut taken had made things more confusing! Unfortunately I am now hitting another wall with the to discount value is a variable so I cannot determine statically how many days to add(I need to get business days), also I cannot find a simple way keep track of hours/minutes... It's what happens when an organization won't train people before giving them responsibilities :p – Maxime Petit Jul 30 '15 at 17:37

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.