+ Start a Discussion
theitdeptrockstheitdeptrocks 

Formula too long! Any ideas?

 

CASE(Plan_Year_End__c,
'12/31', DATE(YEAR(TODAY()),10,25),
'1/31', DATE(YEAR(TODAY()),11,25),
'2/28', DATE(YEAR(TODAY()),12,23),
'3/31', DATE(YEAR(TODAY()),1,23),
'4/30', DATE(YEAR(TODAY()),2,22),
'5/31', DATE(YEAR(TODAY()),3,25),
'6/30', DATE(YEAR(TODAY()),4,24),
'7/31', DATE(YEAR(TODAY()),5,25),
'8/31', DATE(YEAR(TODAY()),6,25),
'9/30', DATE(YEAR(TODAY()),7,25),
'10/31', DATE(YEAR(TODAY()),8,25),
'11/30', DATE(YEAR(TODAY()),9,24),
DATE(2222,12,22))

 

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

 

forcedotcomforcedotcom

What do you want this formula to actually do?

theitdeptrockstheitdeptrocks

This formula is looking at a value in a picklist and translating that into an actual date.  The picklist determines a month, but I need a date field set to 67 days prior to the last day of the month.  An example is if the picklist is set to 12/31, then the date field should be set to 10/25/2010.

forcedotcomforcedotcom

Thanks - OK try this:

 

 

DATE( 
YEAR(TODAY()),
VALUE(LEFT(TEXT(End_of_Month__c),2)),
VALUE(RIGHT(TEXT(End_of_Month__c),2))
) - 67

 

It will always return the current year, so you may need to change that part but this will overcome the character limit issue.

forcedotcomforcedotcom

** Swap End_of_month__c for Plan_Year_End__c

theitdeptrockstheitdeptrocks

Thanks for the response!

 

I had to change it to this before it would save (Plan_Year_End__c is a picklist and it complained about the "Text(" portion.

 

DATE( YEAR(TODAY()), 
VALUE(LEFT(Plan_Year_End__c,2)), 
VALUE(RIGHT(Plan_Year_End__c,2)) 
) - 67

 The problem is that the picklist isn't formated as "01" through "09" for the month portion, but is "1" through "9."


So the above works fine... as long as the value is "10/31," "11/31," or "12/31."