How to reduce formula field (datetime) size?

Question

I got an error when i am implementing formula field.

Error: Compiled formula is too big to execute (5,480 characters). Maximum size is 5,000 characters

I am trying to write a formula field which uses another referenced formula fields and some other fields inside it, hence the compilation size is longer than 5,000 characters. how to reduce the Formula field(datetime) size of it?

IF((CONTAINS(New_Product__c,"6")||CONTAINS(New_Product__c,"Testaccoys 12") || CONTAINS(New_Product__c,"Testaccoys for 12") || 
CONTAINS(New_Product__c,"90"))&&Resend_Email__c=false, 


DATE( YEAR( Final_Amount_date__c ) + FLOOR( ( MONTH ( Final_Amount_date__c ) + 7 - 1 ) / 12 ), 
MOD( MONTH ( Final_Amount_date__c ) + 7 - 1 + IF( DAY ( Final_Amount_date__c ) >
 CASE( MOD( MONTH( Final_Amount_date__c ) + 7 - 1, 12 ) + 1, 2, 28, 4, 30, 6, 30, 9, 30, 11, 30, 31 ), 1, 0 ), 12 ) + 1, 
 IF( DAY( Final_Amount_date__c ) > CASE( MOD( MONTH( Final_Amount_date__c ) + 7 - 1, 12 ) + 1, 2, 28, 4, 30, 6, 30, 9, 30, 11, 30, 31 ),
 1, DAY( Final_Amount_date__c ) ) ), 


IF((CONTAINS(New_Product__c,"6")||CONTAINS(New_Product__c,"Testaccoys 12") || CONTAINS(New_Product__c,"Testaccoys for 12") || 
CONTAINS(New_Product__c,"90"))&&Resend_Email__c=true,Final_Amount_date__c+10,IF(CONTAINS(New_Product__c,"180")||CONTAINS(New_Product__c,"24")|| CONTAINS(New_Product__c,"Test VITA 12"), 
DATE( year(Final_Amount_date__c ) 
+ floor((month(Final_Amount_date__c ) + 13)/12) + if(and(month(Final_Amount_date__c )=12,13=12),+1,0) , 
if( mod( month(Final_Amount_date__c ) + 13, 12 ) = 0, 12 , mod( month(Final_Amount_date__c ) + 13, 12 )) , 
min( day(Final_Amount_date__c ), 
case(max( mod( month(Final_Amount_date__c ) + 13, 12 ) , 1), 
9,30, 
4,30, 
6,30, 
11,30, 
2,28, 
31 
) 
) 
),null)))

Answer 1

Based on your formula, I'd say it's complex enough that you should seriously consider moving it to Apex instead. Beyond it being too large, formulas like this are tough to maintain (that is, if you need to make a change to it in the future, it's going to be a challenge to do so).

That said, if you insist that you have to use a formula, there are a few things that you can do. Most of it comes down to seeing if you can simplify any portions of your formula, and referencing What are the Compiled Costs of Each Formula Function.

Right away, your outermost IF() structure looks like this:

IF(
    // test
    <condition A> && <condition B>
    // then
    <stuff>
    // else
    IF(
        // test
        <condition A> && !<condition B>
        // then
        <stuff>
        // else
        <stuff>
    )
)

<condition A> is

(
    CONTAINS(New_Product__c,"6")
    || CONTAINS(New_Product__c,"Testaccoys 12")
    || CONTAINS(New_Product__c,"Testaccoys for 12") 
    || CONTAINS(New_Product__c,"90")
)

Each CONTAINS() compiles to 58 characters + 2 for the parenthesis + the character length of the things inside the function (assuming New_Product__c is not a formula field. If it is, then you add the compiled cost of that formula every time you reference that formula field).

All in all, this snippet of your formula consumes at least 2 +6 +5 +5 +(58 +15 +2 +3) +(58 +15 +2 +15) +(58 +15 +2 +19) +(58 +15 +2 +4) = 359 compiled characters, and you largely repeat it a second time. That's a waste, and goes quite a ways towards getting your formula down to size.

We can completely remove that second <condition A> by restructuring your outermost IF() like so

IF(
    <condition A>,
    IF(
        <condition B>,
        <stuff>,
        <other stuff>
    ),
    null
)

That also removes a copy of <condition B>, and a pair of &&. That change alone should bring you down 393 compiled characters (or more of Final_Amount_date__c is a formula field).

Beyond that, we can make more simplifications.

There are a few times where you have (MONTH( Final_Amount_date__c ) + 7 - 1, 12 ).
x + 7 - 1 == x + 6
Simplifying that saves 4 compiled characters each time you do it.

One particular instance of that, which can be simplified further, is YEAR( Final_Amount_date__c ) + FLOOR( ( MONTH ( Final_Amount_date__c ) + 7 - 1 ) / 12 ).

CEILING() and FLOOR() are among the more expensive functions to use (in terms of compiled characters), coming in at 112. What this snippet does is simply adds 1 to the year if Final_Amount_date__c is June or later. There is a more efficient way to do that.

YEAR( Final_Amount_date__c ) + IF( MONTH ( Final_Amount_date__c ) > 6, 1, 0 ) By my count, this simplification saves 91 compiled characters.

Together, that should save 484 compiled characters, which would put you at 4996.

There are still other improvements that could be made (experiment with using CASE() instead of CONTAINS()), but I'll leave the rest to you.

Answer 2

Since you don't seem willing to share your formula. What you can do (if you don't want to reduce your character size) is plead to Salesforce.com themselves and ask them to increase the maximum characters count for formula field and explain your case on https://help.salesforce.com/home :

Once you're on that site :

1. Log in (don't think it will work on Developer Edition, probably Professional edition onwards)
2. Contact Support (as in screenshot below)
3. Then you can create a case of why you need to increase the formula field limit

I have done this before to increase size limit of picklist values (which was only 1,000 values) and they agreed to increase the number of picklist to a certain extent (like 1500 values). So good luck and try to ask them.