+ Start a Discussion
jflatlandjflatland 

Need help fixing broken formula

I have a work flow rule that sets the Close Date on Opportunities that are created by a specific person to 6 months from the Opportunity Created Date. Here is the formula I am using

 

DATE( YEAR(TODAY()) , (MONTH(TODAY()) + 6), DAY(TODAY()))

 

Up until today it has been working just fine, however now that it is July 1st and the new close date would be January 1, 2010 it is causing an error when the specific person noted above tries to create an opportunity. Below is the message they receive.

 

A workflow or approval field update caused an error when saving this record. Contact your administrator to resolve it.: Close Date: value not of required type: common.formula.FormulaEvaluationException: Month or Day out of range in DATE() function

Can anyone give me an idea of why it is no longer working correctly?

Thanks!

ron_reedron_reed

I would assume that it's this part of the formula that is causing the problem:

(MONTH(TODAY()) + 6)

 

Month(Today()) = 7 + 6 = 13 

 

You should be able to put in a condition that says if MONTH(TODAY()) > 6, then use MONTH(TODAY()) - 6) otherwise use MONTH(TODAY()) + 6) for that part of the formula.

marie.tournemarie.tourne

That solution will work today, but if an opportunity is created the 31st of August, the worflow will try to put 31st of February in the close date which won't work.

 

To make the rule a bit more easy, maybe you could set the close date to today + 6 months with day =  last day of the month, the calculation will be easier and it may suit your need as well :

 

IF(Month(TODAY())=6,(DATE(YEAR(TODAY()),12,31)),
IF(Month(TODAY())<6,(DATE(YEAR(TODAY()),MONTH(TODAY())+7,1)-1),
(DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 5,1)-1)))