Validation Rule to Lock Opp 15 Days After a Quarter Ends

Question

Title says it all. I'm looking to write a validation rule that effectively prevents non-System Admins ($Profile.Name <> "System Administrator") from changing the Amount, Close Date or creating new Opportunities if 15 days have passed since the the quarter. They should also be able to modify/create Opps in the current quarter.

The only real fields that shouldn't be modified after that date are Stage and Amount. They should be able to change everything else related to an opportunity.

I got this:

AND(
$Profile.Name <> "System Administrator", 
$Profile.Name <> "API System Administrator", 
$User.FirstName <> "Aram",
OR(
ISNEW(),
ISCHANGED(CloseDate),
ISPICKVAL( PRIORVALUE(StageName) , "Closed Won"),
ISPICKVAL( PRIORVALUE(StageName) , "Closed Lost"),
ISCHANGED(Amount)),
OR(

And I'm a little lost after that.

Answer 1

Some suggestions to make your life easier.

First, go to Setup > Develop > Custom Permissions, create a new permission, which you'll reference in the rule. It could be something like "Edit Time-Locked Opps" or whatever you want to call it.

This means you no longer have to check profile names, and you can assign it via permission sets, too. For example, this rule fragment will return true if the user doesn't have the permission, and thus be denied.

NOT($Permission.Edit_Time_Locked_Opps)

Next, there happens to be a Boolean flag called IsClosed which is set when an opportunity reaches Closed Won or Closed Lost (and any custom closed values you may have). The following fragment returns true when the opportunity is closed, and would prevent edits:

IsClosed

So, ultimately, your rule should look something like:

AND(
  NOT($Permission.Edit_Time_Locked_Opps), 
  IsClosed,
  OR(
    ISCHANGED(Amount), 
    ISCHANGED(CloseDate)
  ),
  OR(
    YEAR(CloseDate) < YEAR(TODAY()-15),
    AND(
      YEAR(CloseDate) = YEAR(TODAY()-15),
      CEILING(MONTH(CloseDate)/4) < CEILING(MONTH(TODAY()-15)/4)
    )
  )
)

Working through this, we see if the user has the custom permission we wrote, then we see if the opportunity is closed (won or lost), if our protected fields changed, and then we see if we count back 15 days, is the resulting year greater than the close date's year. Otherwise, we see if the CloseDate is in the current year, and the FQ is less than the current FQ (again offset by 15 days).