How do I create a date formula to show on a report 3 months prior to an account anniversary date. I have been playing around with the below formula but I can not get it to work
IF(MONTH( Established_Date__c ) + IF(MONTH( Established_Date__c-((365/12) * 3), IF(YEAR(TODAY())) - (YEAR( Established_Date__c )), NULL)
Unfortunately you can't do this. I can't find any documentation from Salesforce on this, but there's an existing answer on success.salesforce.com:
https://success.salesforce.com/answers?id=90630000000grZdAAI
It isn't available in the workflow formula editor either, so it looks like your only option is a regular field and a trigger to update the value whenever a record is created/updated.
While I think this could be done with code relatively easy, I think you can do this without code as well. Here is something you might want to explore:
Create a new formula field called "50% date field. (mt__c is the name of the min term field)
DATE(
IF( MONTH( CloseDate ) + (VALUE(TEXT(mt__c)) / 2) >12, YEAR(CloseDate) + 1,YEAR(CloseDate)),
IF( MONTH( CloseDate ) + (VALUE(TEXT(mt__c)) / 2) >12, FLOOR(MOD(MONTH( CloseDate ) + (VALUE(TEXT(mt__c)) / 2),12)), MONTH( CloseDate ) + (VALUE(TEXT(mt__c)) / 2)),
DAY(CloseDate) + MOD(30*(VALUE(TEXT(mt__c))/2),30))
Create a new Workflow rule on Opportunity object to automatically check the checkbox 0 hours after that formula date.
Create a field update to check the checkbox "Opp 50% expired"; This should happen 0 hours after the formularized 50% date field.
You'll probably need to extend this, or create another workflow rule, to handle the case of when the we're already passed the midpoint of the min term + the close date. That should be considerably easier than this workflow rule.
Best of luck!
-Wes
Best Answer
If you want to know the days left to the next account anniversary, you can use this formula:
This will return the days until the next anniversary date. It will display 0 when it comes the day! So in your report you can just filter the accounts using this field, getting every account which has 90 or less days left.