2

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

4 Answers 4

0

Here are the steps to set up your validation :

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

enter image description here

  1. 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. enter image description here
  2. Select Save and that’s all there is to it.
3
  • 3
    The key sentence from OPs question is Where Date2 is calculated based on other columns, that means Date2 is not available for Validation because it is calculated when the Item is saved. Feb 15, 2016 at 10:17
  • Oh yes...I missed that
    – P S
    Feb 15, 2016 at 10:34
  • I completed this scenario in a test list and using Date2 as a Calculated Column of Date3 + Date4 is valid within list settings validation. I will post screenshots in a separate answer and see if it helps.
    – Hartraft
    Jul 11, 2021 at 23:07
0

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.

1
  • The date2 column is calculated based on other 2 date columns. Consider Date3 & Date4 columns have manually entered date values. So the Date2 = Date3+Date4. Now I need to validate for Date1 like Date1>Date2
    – Arun
    Feb 15, 2016 at 15:05
0

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

0

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: enter image description here

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

List Settings Validation is then setup with: enter image description here

And finally the results showing you can only put in a date1 > date2: enter image description here

Additionally an example of a "New Item": enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.