[SalesForce] Add months to a date using formula field

I have a requirement. On opportunity there is a field Min Term (months) and a closedate field(standard).
Need to create a check box field "Opp 50% expired". It will be auto-ticked when closedate + minTerm/2 > today.

I tried to achieve by creating the formula field but found that we can't add months to a date.
Tried to use some custom formula but they also do not work in all scenarios.

Can we do this using apex ?

Thanks in advance

Best Answer

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:

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

  2. Create a new Workflow rule on Opportunity object to automatically check the checkbox 0 hours after that formula date. enter image description here

  3. Create a field update to check the checkbox "Opp 50% expired"; This should happen 0 hours after the formularized 50% date field.

  4. 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

Related Topic