Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
BuellBuell 

Can anyone shorten this formula?

Code:
DATE (
YEAR(CloseDate) + FLOOR((((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR( CloseDate ) * 12) + MONTH( CloseDate ))) / Term_in_Months__c) + 1) * Term_in_Months__c) + (MONTH(CloseDate) - 1)) / 12),
CASE(MOD(((FLOOR((((YEAR( TODAY() ) * 12) + MONTH( TODAY() )) - ((YEAR( CloseDate ) * 12) + MONTH( CloseDate ))) / Term_in_Months__c) + 1) * Term_in_Months__c) + MONTH(CloseDate),12), 
1,01, 
2,02, 
3,03, 
4,04, 
5,05, 
6,06, 
7,07, 
8,08, 
9,09, 
10,10, 
11,11, 
12),
IF( AND(DAY(CloseDate) = 29,MONTH(CloseDate) = 02) , 28, DAY(CloseDate)))

 Compiles to 18,869 characters, references no custom formula fields.


Message Edited by Buell on 10-02-2008 02:23 PM
Marc PannenbergMarc Pannenberg
Hi Buell,

as you are so far above the limit, simply shortening it won't help. I think you need to think about another way of doing it... Generally, a CASE statement uses up a lot of formula space. In a lot of cases you can save chars by replacing it with IF-statements.

What exactly ARE you tying to do? My eyes glazed over after the 5th nested function... ;)

Marc
BuellBuell
Yeah, my eyes glazed over too while writing it.  I am trying to create a field that shows the next renewal date based off of an opportunity close date and opportunity contract length field.  For example if a deal closed in September of 2007 with a 13 month contract then the next renewal date would be this October, but if it was closed in August of 2007 with a 13 month contract then the next renewal date would be October of next year.  The tricky part is that the contract length field is in months.
Marc PannenbergMarc Pannenberg
I see. I read your other post on this issue. I don't know a good answer, but I'm trying similar things myself. I'll let you know if I come up with something useful.

Marc
BuellBuell
Thank you Marc!
BuellBuell
By the way if you are trying to do the same thing with years let me know... already cracked that nut.