How to subtract months from a date field:
I have a date field on opportunity, called: Current Term End date.
I want to create a Formula field, which will subtract 3 month from Current Term End date and display result in numbers.
Current Term End date - 3 month = days left.
Basically i am displaying, how many days left for that opportunity to close/update something like this.
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
Your formula works fine for me. Just replace all instances of
YEAR(MostRecent_Actual_Install_Date__c) + 1
with
YEAR(MostRecent_Actual_Install_Date__c)
Sample input/output combos:
03-02-2016 / 03-31-2016
02-02-2016 / 02-29-2016
01-02-2016 / 01-31-2016
12-02-2015 / 12-31-2015
Best Answer
With formula fields, date artithemetic is done in days.
Example:
MyDateField__c - 5substracts 5 days.To substract a conceptual month you could do
MyDateField__c - 30to subtract 30 days.However, not every month is exactly 30 days, so if you need very accurate result you will need to consider either:
(a) complicated formula, or
(b) rely on apex to do the date calculation for you, such as in a trigger or have process builder call an invocable apex class whenever the record or field value changes.
Formula Functions
For reference, please see these documentation articles about date/time functions in formula fields:
Apex Methods