Is it normal that a validation rule doesn't work as intended?

Question

We have a few validation rules to protect our records to be modified if they meet the criteria, but a few days ago one of the validation rules stopped working, right now we don't know the exact reason.

Also we tried to debug it and if we remove the OR Section with the stage names, works perfectly, we tried many variations of this and all of those failed.

We're thinking on a workaround creating a new custom field to make the validation rule shorter, but is there any other solution that we're not seeing?

AND(
    NOT($Profile.Name = "Profile A"),
    NOT($Profile.Name = "Profile B"),
    NOT($Profile.Name = "Profile C"),
   OR(
      ISPICKVAL(StageName, "Closed Won"),
      ISPICKVAL(StageName, "Closed Renewal"),
      ISPICKVAL(StageName, "Closed Change Order")),
   OR(
       ISCHANGED(AccountId),
       ISCHANGED(Name),
       ISCHANGED(StageName),
       ISCHANGED(Description),
       ISCHANGED(Amount),
       ISCHANGED(Probability),
       ISCHANGED(CloseDate),
       ISCHANGED(OwnerId)
      )
)

Answer 1

Given that you care about validating whether the Opportunity is moving out of one of the three stagenames, your OR block for StageName needs to change from:

OR(
  ISPICKVAL(StageName, "Closed Won"),
  ISPICKVAL(StageName, "Closed Renewal"),
  ISPICKVAL(StageName, "Closed Change Order"))

to:

OR(
  ISPICKVAL(PRIORVALUE(StageName), "Closed Won"),
  ISPICKVAL(PRIORVALUE(StageName), "Closed Renewal"),
  ISPICKVAL(PRIORVALUE(StageName), "Closed Change Order"))

Notes: PRIORVALUE Help doc

Answer 2

There's a few tricks you can use to optimize this, even though it looks correct as is.

First, consider moving to Custom Permissions. Create a Custom Permission, assign it to any Profiles or Permission Sets you want, then you can optimize the validation rule as:

AND(
  NOT($Permission.EditClosedOpps),
  OR(
    ISPICKVAL(StageName, "Closed Won"),
    ISPICKVAL(StageName, "Closed Renewal"),
    ISPICKVAL(StageName, "Closed Change Order")),
  OR(
    ISCHANGED(AccountId),
    ISCHANGED(Name),
    ISCHANGED(StageName),
    ISCHANGED(Description),
    ISCHANGED(Amount),
    ISCHANGED(Probability),
    ISCHANGED(CloseDate),
    ISCHANGED(OwnerId)
  )
)

Second, consider using CASE as a shortcut to comparing values in a picklist. Unfortunately, you can't return TRUE or FALSE, but we can return, for example, a number, so we'll use the 1 = CASE() model.

AND(
  NOT($Permission.EditClosedOpps),
  1 = CASE(
    StageName, 
    "Closed Won", 1, 
    "Closed Renewal", 1, 
    "Closed Change Order", 1, 
    0
  ),
  OR(
    ISCHANGED(AccountId),
    ISCHANGED(Name),
    ISCHANGED(StageName),
    ISCHANGED(Description),
    ISCHANGED(Amount),
    ISCHANGED(Probability),
    ISCHANGED(CloseDate),
    ISCHANGED(OwnerId)
  )
)

Third, consider making this eight validation rules instead of one. This allows you to attach specific errors to specific fields, making it obvious which fields are disallowed, rather than a generic error at the top of the page.

AND(
  NOT($Permission.EditClosedOpps),
  1 = CASE(
    StageName, 
    "Closed Won", 1, 
    "Closed Renewal", 1, 
    "Closed Change Order", 1, 
    0
  ),
  ISCHANGED(AccountId)
)

...

AND(
  NOT($Permission.EditClosedOpps),
  1 = CASE(
    StageName, 
    "Closed Won", 1, 
    "Closed Renewal", 1, 
    "Closed Change Order", 1, 
    0
  ),
  ISCHANGED(Name)
)

... etc

This design will also make it easier to troubleshoot if a specific validation rule is not working, and allows them to be tweaked individually.