Formula Validation Rule – Prevent Editing Closed Opportunities with Invalid Close Date

formulavalidation-rule

Need: I am trying to write a validation rule that prevents the editing of Closed Opportunities with a Close Date outside of the current calendar month/year, except for certain users. In other words, only 4 specific users should be able to edit a Closed Opportunity if the Close Date is outside of the current calendar month/year.

Problem: I have figured out month piece, but am struggling to get the year part. The validation rule I have written will kind of work. However, if another user changes the Close Date to the same month, but different year, then they can edit the Opportunity.

My current validation rule is below. Does anyone know how to include the current year piece? Thank you so much for taking the time to look.

AND(

PRIORVALUE( IsClosed ) = True,

MONTH(CloseDate)<>MONTH(TODAY()),

NOT(OR(

$User.Alias="aaaaa",

$User.Alias="bbbbb",

$User.Alias="ccccc",

$User.Alias="ddddd"
)))

Best Answer

You could potentially attack the problem from a slightly different angle, instead of comparing the current month() value, you could check that the date is between the start of the close month and less than the start of the following month. Using AddMonths(date, numMonths) means that this takes into account different lengths of month + year crossover such as December close dates.

AND(

PRIORVALUE( IsClosed ) = True,

OR( DATE(YEAR(CloseDate),MONTH(CloseDate), 1 ) > TODAY(),

ADDMONTHS(DATE(YEAR(CloseDate),MONTH(CloseDate), 1), 1) <= TODAY())

NOT(OR(

$User.Alias="aaaaa",

$User.Alias="bbbbb",

$User.Alias="ccccc",

$User.Alias="ddddd" )))

This would then validate for only edits made during the month of the close date including the year of the opportunity.

Related Topic