SharePoint Online List Column Validation Examples

In this SharePoint tutorial, we will discuss a few SharePoint list column validation examples, how to implement list column validation in SharePoint Online/2013/2016. We will also see an example of “SharePoint column validation date must be greater than today”.

Here, I have taken 3 different list column validation examples in SharePoint.

SharePoint List Column Validation Example-1

In the first example, we will see how to allow particular value in SharePoint list column, else it will show a validation error.

Here we can see how a list column validation works in SharePoint Online/2013/2016.

I have a SharePoint 2013 list which contains a Title column, I want user should be able to put only “Mr” or “Mrs”. If users put something else other than this, then it should give an error message saying You can put only Mr or Mrs.

To use validation for a particular column, follow the below steps:

Open your SharePoint List, then go to the List Settings page. In the Settings page, go to the Columns section and then click on the particular column for which you want to use the validation. In my case, it is the Title Column.

sharepoint list validation examples
SharePoint list validation examples

This will open the Edit Column page, here click on “Column Validation“. Then in the Formula box put like below:

=OR(Title=”Mr”,Title=”Mrs”)

And in the User message, put the message which the user wants to see if the validation fails like: “Please only put Mr or Mrs”.

The formula should look like below:

sharepoint column validation multiple conditions
SharePoint column validation multiple conditions

Then click on OK and Save the formula.

Now try to add one item to the SharePoint list. If you put any other thing apart from “Mr” or “Mrs” in the Title column, it should display the message like below:

sharepoint column validation examples
SharePoint column validation examples

SharePoint List Column Validation Example-2 (Age validation)

Now, in this example, we will see how we can validate Age using SharePoint list column validation.

If you want to validate another column, let’s say you have an age column and you want to show a validation message if user put value less than 25.

You can write in the Column validation formula like below:

=(Age>25)

It should look like this:

sharepoint 2013 column validation examples
SharePoint 2013 column validation examples

Now when a user tries to put value less than 25, it will show a message like below:

sharepoint online column validation examples
SharePoint online column validation examples

SharePoint List Column Validation Example-3 (Require at least one column validation)

Now, we will see how we can implement “Require at least one column validation” in a SharePoint list column.

Here I have a Contact list in my SharePoint Online site. In that list, 3 columns are there like:

  • Home Phone
  • Business Phone
  • Mobile Phone

The requirement here is that a user has to enter at least one phone number.

Here we can use list validation. To implement list validation, follow below steps.

Go to the SharePoint list settings page and click on “Validation settings” which are under General Settings like below:

SharePoint List Column Validation
SharePoint List Column Validation



Then in the Validation Settings page write the formula like below:

=COUNTA([Home Phone],[Business Phone],[Mobile Number])>=1

And you can also put the error message in the “User Message” section like below:

Sharepoint list column validation at least one column validation example
Sharepoint list column validation at least one column validation example

Click on Save, to save the formula.

Now, when a user tries to submit without giving a phone number, the validation will trigger like below:

Require at Least One Field in SharePoint list column validation
Require at Least One Field in SharePoint list column validation

Similarly, if you want to validate other scenarios like the user should enter either mobile number or email id, then you can write a formula like below:

=COUNTA([Emil ID],[Mobile Number])>=1

You can check out a few examples of common formulas in SharePoint Lists here.

SharePoint column validation date must be greater than today

Now, we will see the SharePoint column validate date example, we will see how we can validate that date must be greater than today using SharePoint column validation.

Here I have a SharePoint Online list which has a Due Date column, here we will implement validation to the date column so that user can select date must be greater than today.

Open the SharePoint Online list, Go to the List Settings page. And then choose Validation settings which is under General Settings.

Then in the Validation Settings page, Add the below formula and a user message like below:

Formula: [Due Date]>Today()

User Message: Due date should be greater than today

sharepoint column validation date
SharePoint column validation date

Now, when a user selects a date which is less than today, then you can see an error message like below:

sharepoint column validation date must be greater than today
SharePoint column validation date must be greater than today

Hope this example, helps to “SharePoint column validation date must be greater than today”.

Similarly, if you want to check the Due Date should be greater then created date, then you can write the formula like below:

=[Due Date] > [Created]

SharePoint column validation: Start date and end date validation (AND clause in List Validation)

Now, we will see how we can do SharePoint column validation for start date and end date. In this particular example, we will see how use should enter due date should be greater than today and less than 30days.

We will also see how we can use AND clause in list validation in SharePoint.

Open the SharePoint Online list, Go to the List Settings page. And then choose Validation settings which is under General Settings.

Then in the Validation Settings page, Add the below formula and a user message like below:

=IF([Due Date]>Today(),IF(DATEDIF(Today(),[Due Date],”d”)<=90,TRUE,FALSE),FALSE)

SharePoint column validation: Start date and end date validation
SharePoint column validation: Start date and end date validation

Now, if a user enters Due Date which is greater than 30 days then you can see an error like below:

SharePoint column validation: Start date and end date validation
SharePoint column validation: Start date and end date validation

You may like following SharePoint tutorials:

Hope this article will be helpful to implement list column validation in SharePoint Online/2013/2016. Also, we saw a few SharePoint list column validation examples.

  • Hello,

    I am trying to add a column validation for number entry which equal to 13 digits.
    I tried the function as below but it is not working. Can you please help me ?

    =AND(LEN([E-Ticket Number])=13;ISNUMBER([E-Ticket Number]))

    Thanks

  • I am trying to add a validation for the user must choose an Outcome as well as an Outcome date, you cant have one without the other? Help?

  • >