Error: Compiled formula is too big to execute (21,063 characters). Maximum size is 15,000 characters

Question

I am trying to save the following formula field but it is giving me error.

TEXT(
MOD(
FLOOR(
TT_Calculate_Time_Elapsed__c), 24)
) & " Hours: " &
TEXT(
ROUND(
MOD(TT_Calculate_Time_Elapsed__c,1) * 60
, 0
)
) & " Mins"

It gives me the following error:

Error: Compiled formula is too big to execute (21,063 characters). Maximum size is 15,000 characters

Below is the formula field used in formula above:

TT_Calculate_Time_Elapsed__C

IF(
ISPICKVAL( Status__c , "Closed") ,

( ROUND( 8.5 * ( ( 5 * FLOOR( ( DATEVALUE( Closed__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( Closed__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8.5 * ( MOD( Closed__c - DATETIMEVALUE( '1900-01-08 08:30:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( Opened__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( Opened__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8.5 * ( MOD( Opened__c - DATETIMEVALUE( '1900-01-08 08:30:00' ), 1) ) )
)
)
),2) )

,

( ROUND( 8.5 * ( ( 5 * FLOOR( ( DATEVALUE( NOW() ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( NOW() ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8.5 * ( MOD( NOW() - DATETIMEVALUE( '1900-01-08 08:30:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( Opened__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( Opened__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8.5 * ( MOD( Opened__c - DATETIMEVALUE( '1900-01-08 08:30:00' ), 1) ) )
)
)
),2) )
)

Answer 1

It would be good to have another look at your actual requirement, because the way these formulas are set up looks rather (overly) complex to me.

However, as a quick solution, I'd look at reducing the size of the TT_Calculate_Time_Elapsed__C formula field. It is largely a repetition of 2x the same block of logic, with slight differences between whether the record has status 'Closed' or not.

So I think it would be more efficient to replace the occurrences of the 'Closed__c' with a mini-decision element: So take the first block only, and replace

Closed__c

by

IF(TEXT(Status__c) = "Closed",Closed__c,NOW())

that would result in the following formula:

( ROUND( 8.5 * ( ( 5 * FLOOR( ( DATEVALUE( IF(TEXT(Status__c) = "Closed",Closed__c,NOW()) ) - DATE( 1900, 1, 8) ) / 7) +
MIN(5,
MOD( DATEVALUE( IF(TEXT(Status__c) = "Closed",Closed__c,NOW()) ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 8.5 * ( MOD( IF(TEXT(Status__c) = "Closed",Closed__c,NOW())- DATETIMEVALUE( '1900-01-08 08:30:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATEVALUE( Opened__c ) - DATE( 1900, 1, 8) ) / 7) +
MIN( 5,
MOD( DATEVALUE( Opened__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 8.5 * ( MOD( Opened__c - DATETIMEVALUE( '1900-01-08 08:30:00' ), 1) ) )
)
)
),2) )

To make it even clearer, you could actually create a formula field 'Enddate for time elapsed' and use that in the formula instead of repeating IF(TEXT(Status__c) = "Closed",Closed__c,NOW()). The size will be the same, but the formula will be a bit cleaner.