0

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) )
)
6
  • Seems like TT_Calculate_Time_Elapsed__c is a formula as well. Please also post its contents. Mar 8, 2023 at 11:26
  • I updated the question
    – Marjan
    Mar 8, 2023 at 11:34
  • and probably those 2 Processing_Time fields are formula fields as well? If so, you need to post those formulas as well, because all the formula sizes are added up on the 'parent' formula level. Mar 8, 2023 at 11:38
  • Please check now
    – Marjan
    Mar 8, 2023 at 11:42
  • 2
    You should probably also post your requirements. The message is clear, the formula is too complex. You either need to simplify the formula or move some of that processing to Apex/Flow. If you post your requirements it would be easier for someone to help you toward how to proceed.
    – CyberJus
    Mar 8, 2023 at 13:11

1 Answer 1

2

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .