function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Paul F.ax382Paul F.ax382 

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)))))
 
Regards
 
 
Paul
Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell
This ought to do the trick:

Code:
DATE(
YEAR (AppDate__c) + (FLOOR((TODAY() - AppDate__c) / 365)  + 1),
MONTH(AppDate__c),
IF(AND(DAY(AppDate__c) = 29,MONTH(AppDate__c ) = 02) , 28, DAY(AppDate__c)))

Similar date calculations HERE.


Message Edited by Buell on 09-29-2008 09:29 AM

All Answers

FreshFresh
Looking at your current formula, you actually have a character count of about 400-500. I would guess that the reason you are exceeding the limit is that the date you are referencing is actually a formula itself, and everytime you reference it Salesforce basically puts in the whole formula (translation: if you have a formula that is 200 characters long and you reference it 6 times in another formula, that would count as 1200 characters).

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!
Paul F.ax382Paul F.ax382
Fresh,
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
DaveEDaveE

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
Paul F.ax382Paul F.ax382

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
BuellBuell
This ought to do the trick:

Code:
DATE(
YEAR (AppDate__c) + (FLOOR((TODAY() - AppDate__c) / 365)  + 1),
MONTH(AppDate__c),
IF(AND(DAY(AppDate__c) = 29,MONTH(AppDate__c ) = 02) , 28, DAY(AppDate__c)))

Similar date calculations HERE.


Message Edited by Buell on 09-29-2008 09:29 AM
This was selected as the best answer
Paul F.ax382Paul F.ax382
Buell,
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
Paul F.ax382Paul F.ax382
Just an improvement to formula in previous posts that more precisely calculates the date. 

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
PerGeertPerGeert

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)))

 
BuellBuell
Awesome!