Fire validation rule if the selected date is not within 30 days

validation-rule

I'm trying to write validation rule which states that,

schedule_date__c should be within 30 days from start_date__c and end_date__c

I have done the following but not sure why the validation is not working.

Validation rule:

AND(schedule_date__c,
((start_date__c - end_date__c) < 30))

Best Answer

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.

So in your Validation Rule, you checking for this:

  1. Is there a value in the schedule_date__c field?

AND

  1. Is the difference between the start_date_c and end_date__c values less than 30 [days]?

There are a couple of issues:

  1. You are not directly comparing schedule_date__c to anything to do with Start or End Dates.

  2. Subtracting End Date from Start Date (assuming that End Date is always later than Start Date) will always be less than 30, because the answer will always be negative.

If by

schedule_date__c should be within 30 days from start_date__c and end_date__c

you mean that Schedule Date should be inside of 30 days after the Start Date or inside of 30 days before the End Date, then the following Validation Rule formula should work*:

OR(
   (schedule_date__c - start_date__c) > 30,
   (end_date__c - schedule_date__c) > 30
)

* assuming that all the fields are the same Date type

UPDATE

After chat discussion, requirements were clarified and some "givens" determined.

GIVENS

  • End Date will always be after/greater than Start Date (separate validation)
  • A Schedule Date value is not allowed if there is no Start Date and End Date (separate validation)

REQUIREMENTS

  • Schedule Date must be after/greater than the End Date
  • Schedule Date must be within 30 days of the Start Date

VALIDATION RULE

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

What this does:

  • If Schedule Date has a value [user selected something]... AND
  • Schedule Date is on or before the End Date -OR- Schedule Date is greater than 30 days after Start Date...

...then the formula returns TRUE and the error is displayed:

enter image description here

Related Topic