validation rule: check two different dates and restrict date selection

Question

I have three date fields schedule_date__c, start_date__c, and end_date__c

the logic and different scenarios:

1. if the start_date and end_date both has selected (meaning has date) then schedule_date should be within 10 days from the end_date
2. if the start_date is selected (meaning has date) and end_date has blank then schedule_date should be within 10 days from the start_date

for an example 1:

start_date : 09/01/2021 end_date : 09/15/2021

schedule_date : should be within 09/25/2021 (we are restricting based on the end_date)

for an example 2:

start_date : 09/01/2021 end_date : BLANK (not selected)

schedule_date : should be within 09/10/2021 (we are restricting based on the start_date)

AND(
  ISBLANK(end_date__c),
  OR(
    schedule_date__c <= start_date__c,
    (schedule_date__c - start_date__c) > 10
  ),
  NOT(ISBLANK(end_date__c)),
  OR(
    schedule_date__c <= end_date__c,
    (schedule_date__c - end_date__c) > 10
  )
)

Answer 1

The AND logical function is described as this:

Returns a TRUE response if all values are true; returns a FALSE response if one or more values are false.

The logic operators and functions in Salesforce formulas (and thus validation rules) are similar to Reverse Polish Notation in math: The operator comes first, then you comma-separate everything that the operator is to act on or consider.

So instead of being like is normally typed in most code: this AND that, it is instead: AND(this, that).

That means in your Validation Rule, you are currently checking for this:

Does end_date__c NOT have a value

AND

(Is start_date__c later than schedule_date__c
   OR
 Is the difference between schedule_date__c and start_date__c more than 10 days)

AND

Does end_date__c have a value

AND

(Is end_date__c later than schedule_date__c
   OR
 Is the difference between schedule_date__c and end_date__c more than 10 days)

As you can see, there will never be a time when end_date__c both does and does not have a value, so this validation rule should never fire.

---

Analysis

Taking your statements on when you want the validation rule to fire and adding modifying them slightly:

1. if (start_date AND end_date are NOT BLANK) then (schedule_date should be later than end_date AND schedule_date should be within 10 days after the end_date)

OR

2. if (start_date has a value AND end_date IS BLANK) then (schedule_date should be later than start_date AND schedule_date should be within 10 days after the start_date)

Pseudocode

Remembering that validation rules fire when the formula evaluates to TRUE, and that TRUE is what causes the error message to be shown and prevents the record from being saved...

If

   NOT(ISBLANK(start_date__c))
      AND 
   NOT(ISBLANK(end_date__c)) 

then fire rule if

   schedule_date__c <= end_date__c
      OR
   schedule_date__c - end_date__c > 10

If

   NOT(ISBLANK(start_date__c))
      AND
   ISBLANK(end_date__c)

then fire rule if

   schedule_date__c <= start_date__c
      OR
   schedule_date__c - start_date__c > 10

Validation Rule

OR(
   AND(
      NOT(ISBLANK(start_date__c)),
      NOT(ISBLANK(end_date__c)),
      OR(
         schedule_date__c <= end_date__c,
         schedule_date__c - end_date__c > 10
      )
   ),
   AND(
      NOT(ISBLANK(start_date__c)),
      ISBLANK(end_date__c),
      OR(
         schedule_date__c <= start_date__c,
         schedule_date__c - start_date__c > 10
      )      
   )
)

Testing

---

Notes

• The Validation Rule could be "simplified" (logically) by moving NOT(ISBLANK(start_date__c)) out (since it is repeated) and putting it in a surrounding AND. I tested this version with the exact same dates and got the exact same results. However, it majorly complicates it visually, so I am not including it.
• The error message you give to the user putting all of this into one Validation Rule will be quite busy if you make it at all helpful to explain why the record will not save. So you either need to give a not-so-helpful error message (I don't like this option), or make this be two separate Validation Rules - each with concise helpful text (I like this option).