Wednesday, July 17, 2019

Validation Rules using Date fields on a Sharepoint List

I have a requirement to restrict users for entering Review date as below

  1. Only Thursdays are allowed
  2. Need to skip the coming first Thursday
  3. Can't be farther from 4 Thursdays from today
I used the available validation settings rule to restrict user and it is as below. For this, created a date field 'CreatedDate' with default value set to today and hidden it in form as the 'Created' column not working in Validation rules in Edit forms.

=IF(WEEKDAY([ReviewDate])=5,IF([ReviewDate]-[CreatedDate]<28,IF([ReviewDate]-[CreatedDate]>=7,TRUE,FALSE),FALSE),FALSE)

In above formula I used Review date and also the created date.
  1. WEEKDAY([ReviewDate])=5 - its to make sure the selected date is thursday only, the weekdays start from Sunday with 1
  2. [ReviewDate]-[Created]<28 - We are making sure the review date is no more than 28 days so that it doesn't cross 4 Thursdays.
  3. [ReviewDate]-[Created]>=7 - This is to restrict the user from selecting first Thursday