0

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" )))

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
4
  • Thank you so much for the suggestion. However, when I tried this and logged in as a user not in the validation rule, I was still able to edit a Closed Lost Opportunity with a close date of 11/30/2022 and change the Stage as well as the date to outside of December.
    – NoviceAdmin80
    Dec 2, 2022 at 3:27
  • Hi @NoviceAdmin80 i have quickly updated the logic to validate against dates outside the current month, the concept is still similar to check for dates outside of the current month.
    – Michael Fleming
    Dec 2, 2022 at 3:56
  • This worked! However, there is one more thing I forgot to mention. When I logged in as a user not in the validation rule, I was able to poke a hole in it by changing the Close Date to the current month. This unlocked the Opportunity to be edited. Do you know of a way to augment your validation rule to prevent them from editing the Close Date to the current month, as well as all other fields? I tried adding an ISCHANGED(CloseDate) line, but that made all other fields editable but the close date. So sorry for not clarifying this in my question. Thank you so much!
    – NoviceAdmin80
    Dec 2, 2022 at 5:25
  • We decided to use your formula as is. We are pretty confident our sales reps won't be able to figure out moving the close date to the current month. Thank you so much one again! This was extremely helpful and I really appreciate it. Have a great weekend!
    – NoviceAdmin80
    Dec 2, 2022 at 19:49
0

Try with below formula.

AND(
IsClosed=True,
ISCHANGED(CloseDate),
YEAR(PRIORVALUE( CloseDate )) <> YEAR ( TODAY() ),
MONTH(PRIORVALUE( CloseDate )) <> MONTH ( TODAY() ), 
OR($User.Alias<>"aaaaa",
   $User.Alias<>"bbbbb",
   $User.Alias<>"cccc",
   $User.Alias<>"dddd")
)
Improve this answer
1
  • Bahdi - Thank you so much for looking at ny question and offering this solution. However, I tested it out, logged in as another user, and was able to edit a closed Opportunity outside of the current month. I'm not sure why because this looks really good. Thank you again.
    – NoviceAdmin80
    Dec 3, 2022 at 19:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .