You need to sign in to do that
Don't have an account?

Calulating Anniversary Date
First time poster & new to SFDC.
For a contact I am trying to calculate the next anniversary date based on a historical registration date (which could be many years previous), to drive a workflow for a reminder e-mail and follow up task to record owner using the Time Based work flow.
The formula that I have used previously, suggested in SFDC documentation (to calculate Next birthday), calculates correctly when using the standard contact Birthdate field, but when I apply the same formula to a custom date field the compilation exceeds the maximum 5000 characters (Error: Compiled formula is too big to execute (5,262 characters). Maximum size is 5,000 characters)
Current formula below and any suggestions on reducing formula size or alternate approaches will be appreciated greatly.
IF(MONTH(AppDate__c)>MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(AppDate__c),DAY(AppDate__c)),
IF(MONTH(AppDate__c)<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)),
IF(DAY(AppDate__c) >= (DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(AppDate__c),DAY(AppDate__c)),
DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)))))
IF(MONTH(AppDate__c)<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)),
IF(DAY(AppDate__c) >= (DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(AppDate__c),DAY(AppDate__c)),
DATE(YEAR(TODAY())+1,MONTH(AppDate__c),DAY(AppDate__c)))))
Regards
Paul
Message Edited by Buell on 09-29-2008 09:29 AM
All Answers
If I am correct that this is the problem, put another date field on the record (but not on the page layout) and have a workflow that copies the formula value to that date field every time the record is edited. Then, run your formula off of the new date field.
Good Luck!
Thanks for the response.
The AppDate field referred to in this formula is just a regular input date field and not calculated.
I have the exact same formula successfully working to calculate a contacts Next Birthday based on the standard Contact BIRTHDATE field, but for some reason same does not work on this one.
IF(MONTH(Birthdate)>MONTH(TODAY()),DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)),
IF(MONTH(Birthdate)<MONTH(TODAY()),DATE(YEAR(TODAY())+1,MONTH(Birthdate),DAY(Birthdate)),
IF(DAY(Birthdate) >= (DAY(TODAY())),DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)),
DATE(YEAR(TODAY())+1,MONTH(Birthdate),DAY(Birthdate)))))
No Syntax errors for this one and compile size of 4848.
Can't fathom why the result should be different and result in a 5262.
Is this possibly a bug with SFDC?
Paul
Did you get resolution on this issue? I am having a very similar problem where I have a formula with multiple IF statements working with data input fields. The exact formula works in two other Formula field types, but not in the third (I need to have it in 24 fields).
I removed all formula field references in the formula but still get the "Referring Formula: Compiled formula is too big to execute...." error. When I select the "Check Syntax" button prior to saving, I get no syntax errors (Complied size: 1,822 characters).
An interesting note: the Error message says the formula size is 5,496 which is almost exactly 3 times the compiled size of 1,822. 3 happens to be how many times I've used the formula. Don't know if this is a coindence or not....
Any guidance would be much appreciated.
Message Edited by DaveE on 09-11-2008 03:37 PM
Dave,
No real joy yet. I am currently living with a bit of work around to my formula that has the date I calculate wrong for the remainder of the same month once it has occurred. Which is unattractive, but acceptable to me given I have the calculated date hidden from the user and does not impact on workflow I am driving from it.
There is obviously a key relationship between how many times the IF statement is called and the size, but based on your comments and my experience this seems influenced by the type or attributes of the fields.
Sorry, still interested for any assistance or suggestions to resolve from the broader group, as I plan on using similar date calc elsewhere, but no magic answer here as of now.
Paul
Message Edited by Buell on 09-29-2008 09:29 AM
Sorry to be MIA for a while, but other duties have called.
Case Closed. I have implemented your suggested formula and it calculates the next application date correctly in all cases & solves my issue.
I really appreciate your time & sharing your knowledge.
Hopefully I can return the favour one day.
Great community support. Thank you
Paul
DATE(
YEAR (AppDate__c) + (FLOOR((TODAY() - AppDate__c) / 365.2425) + 1),
MONTH(AppDate__c),
IF(AND(DAY(AppDate__c) = 29,MONTH(AppDate__c ) = 02) , 28, DAY(AppDate__c)))
Good luck
I ran into the same problem the other day, but re-did the formular (here using the birthdate field). This cut about 2000 charcaters of the size:
IF(OR(MONTH(Birthdate)>MONTH(TODAY()),AND(MONTH(Birthdate)=MONTH(TODAY()),DAY(Birthdate) >=DAY(TODAY()))),DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)),DATE(YEAR(TODAY())+1,MONTH(Birthdate),DAY( Birthdate)))