Formula compile error with lots of dates

Question

I am receiving this error: Error: Invalid Data. Review all error messages below to correct your data. Formula(s) that reference this field are no longer valid: Compiled formula is too big to execute (5,573 characters). Maximum size is 5,000 characters (Related field: Formula)

I have a formula to calculate whether NZ day light savings is on for a date. This is the logic: If the dateis after may and it's after the last sunday of septemeber. Or If the date is before may and before the 1st sunday in April

if(
Month( Attitude_presentation_date__c )>5
&&
Attitude_presentation_date__c>=
(DATE(YEAR( Attitude_presentation_date__c ),9+1,01) - 1
-
MOD( DATE(YEAR( Attitude_presentation_date__c ),9+1,01) - 1 - DATE(1900, 1, 7), 7))
,1,0)


+

if(
Month( Attitude_presentation_date__c )<5
&&
Attitude_presentation_date__c<=
(DATE(YEAR(Attitude_presentation_date__c ),4,01)
+
CASE( MOD( DATE(YEAR(Attitude_presentation_date__c ),4,01) - DATE(1900, 1, 7), 7), 0, 0, 1, 6, 2, 5, 3,
4, 4, 3, 5, 2, 6, 1,0))

,1,0)

I Then have a second formula that uses it to create calendar entries:

DATETIMEVALUE( 
TEXT(YEAR(Attitude_presentation_date__c))+"-" + 
TEXT(MONTH(Attitude_presentation_date__c))+"-" + 
TEXT(DAY(Attitude_presentation_date__c))+" "+ 

Start_time__c
+":00") 

-
if( Date_NZ_Daylight_savings_on__c >0, 1/24*13,1/24*12)

How can I modify this to get it under the 5000 compile limit?

Answer 1

Assuming that it's the second formula that's throwing the compile size error, try moving the first formula into a workflow field update or process instead of a formula field. The steps for a workflow field update are:

1. Create a custom field of the type your field update formula will return (I don't think you can create a WF field update for checkbox, so you might need to make it numeric -- e.g. 1 if true and 0 if false).
2. Create a workflow rule on the object. Set it to execute whenever a record is created or updated.
3. Create a Field Update workflow action. Choose the custom field you created in Step 1 as the target, and use your first formula as the content.
4. In your formula field, replace the part of the formula you copied to the field update action with a reference to your custom field.

When you reference another formula field, it pulls the compile size of that formula into the new formula. When you use a workflow field update, the size of the field is much smaller because it's just a regular field (not compiled at the time it's referenced).

See the Tips for Reducing Formula Size tip sheet in the Salesforce Help (the last section, "If all else fails use a workflow field update").