Date field validation on SharePoint OOTB list

Question

I've sharepoint OOTB list, where I want to validate a date column based on other date column. For EX: consider two columns like Date1 & Date2. Where Date2 is calculated based on other columns. And I need following validation done at Date1 column.

Date1>Date2

I've tried with column validation, but in SharePoint there is some restriction that I can't use another column on column validation. Pls help to achieve it.

Thanks,

Arun

Answer 1

Here are the steps to set up your validation :

1. Navigate to your List Settings and Select ‘Validation settings’ under the General Settings heading:

2. This will take you to your Validation Settings page where you can enter your Formula and a message to be displayed to your user if the validation check fails. Enter your formula in the Formula column and in the User Message column enter the message to be displayed to the user if the validation fails.
3. Select Save and that’s all there is to it.

Answer 2

You can use multiple columns in form validation, as described by @PS. There are some restrictions with using calculated columns.

Let's say Date2 is calculated column with formula:

=Today()

The following validation formula is incorrect, because it uses calculated column:

=[Date1]>[Date2]

What you can do, is copy Date2 formula into the validation. This one works:

=[Date1]>Today()

I could not find any reliable source to confirm it, but from what I've seen, not every calculated column is forbidden. If you set up Date2 as:

=Date(2016, 02, 15)

You'll be able to use it in validation.

Answer 3

There is workaround where you can do the validation using jquery, where you can write down all the logic in SharePoint PreSaveItem() function

Sample Snippet:

 function PreSaveItem() {
       var date1=$(["id ^= date1field"]).val();
       var date3= $(["id ^= date3field"]).val();
       var date4=$(["id ^= date4field"]).val();
       var date2=date3+date4;
      if(date1 > date2){
          return true;
        }
   else{
     alert('error message');
     return false;
     }

    }

Note :Date field conversion should be done for comparision

Answer 4

Sorry all, I have no idea why this came up at the top of my list, just looked at the Date of the question, massive necro!

As others have mentioned if you go to List Settings > Validation Settings you can set a formula to validate the Item in the List which allows referencing multiple columns and in this case it works with a calculated column of Date3+Date4.

The setup of the columns is below:

With the calculation of Date2 as follows, make sure it is set to be Date for data type:

List Settings Validation is then setup with:

And finally the results showing you can only put in a date1 > date2:

Additionally an example of a "New Item":