SharePoint Column Validation Examples

Update 11/2/2015… added "Date must be the first day of the month" and "Date must be the last day of the month".
The following applies to SharePoint 2007, 2010 and 2013.

Column Validation

SharePoint does not include column types for phone numbers or part numbers, nor does it include support for Regular Expressions to test for character patterns. It does support Excel style functions that we can use to create useful column validation formulas.
Below you will find column validation examples for:
  • OR
  • AND
  • Length (LEN)
  • Pattern matching using SEARCH and FIND
  • Date testing

General Validation Formula Rules:

  • Formula must return True or False.
  • Column validations can only be added to Single Line of Text, Number, Choice (Drop-Down menu or Radio buttons, but not Checkboxes), Currency and Date and Time columns.
  • Expressions are generally Excel compatible, but not all Excel functions can be used.
  • Field names without special symbols can be entered as is or in square brackets
          = Price * [Qty]  > 100
  • Field namess with spaces or symbols must be enclosed in square brackets
          =OR( [Sales Region] = 1, [Sales Region] = 1)
  • The text comparisons are not case sensitive.
          =OR( status = "a", status="c")     is true for either "A" or "a" or "C" or "c".
  • In a column validation the formula cannot refer to another column.
  • In a list / library validation the formula can refer to other columns in the same item.

Examples using "OR":

The OR function accepts two or more Boolean tests that each return True or False. OR returns True if any one of the tests is True.
=OR(State="OH", State="IN", State="KY", State="MI")
=OR(Qty=5, Qty=10, Qty=20)

Examples using "AND":

The AND function accepts two or more Boolean tests that each return True or False. AND returns True if all of the tests are True.
=AND(YourFieldName>"A", YourFieldName<"M")     YourFieldName value must be between A and M.
=AND(Qty>5, Qty<100, Qty<>47)      Qty must be between 5 and 100, but not 47.

Examples using "LEN":

As an example, if your part numbers are always 9 characters long:
    =LEN(YourFieldName) = 9
If the part numbers can be 9 or 12 characters long:
    =OR( LEN(YourFieldName) = 9, LEN(YourFieldName) = 12 )

Examples for Pattern Matching

The SEARCH function:  (online help)
  • Matches a pattern using "*" and "?". "*" equals zero more characters and "?" equals exactly one character.
  • To match an asterisks or question mark character prefix the symbols with "~". 
    Example: "a~?b?c" matches "a?bxc" but not "axbxc". 
  • An "*" is assumed to be appended to the end of the match pattern. To limit the length use the AND and LEN functions.
  • The comparison is not case sensitive.
  • If there is a match, the function returns the position of the match. If the every character is to be matched you would typically test for "=1" or maybe ">0". 
  • If there is no match, the function returns ERROR, therefore it must be wrapped inside of an ISERROR function. As we will have a match if there is no error, the ISERROR must be wrapped inside of a NOT function. (online help for ISERROR)
Must start with an "a" or "A" and the third character must be a "c" or "C":
   =NOT(ISERROR( SEARCH("A?C",YourFieldName)=1 ))
   Matches: abc   AbC  aXc  a6c aBcDEF
   Does not match:   bbb   abb  ac  a
Match a phone number pattern of xxx-xxx-xxxx: (note: user could type letters or digits or type extra characters.)
   =NOT(ISERROR( SEARCH("???-???-????",YourFieldName)=1 ))
   Matches: 123-123-1234    aaa-aaa-aaaa   123-123-12344444
Match a phone number pattern of xxx-xxx-xxxx and limit the length:
   =AND( NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))), LEN(YourFieldName)=12 )
   Matches: 123-123-1234
   Does not match: 123-123-12345

Match a phone number and make sure only digits have been used:
The first example here is not a true pattern match. It just extracts the characters we think should be digits and tries to multiply them by any number. If that fails, then one or more of the characters is not a number. (online help for CONCATENATE and MID)
   Matches: 123-123-1234    123x123x1234   123-123-1234xxxxx
   Does not match: abc-123-1234
The second example combines the earlier pattern match with a numeric test:
   =AND(NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))),LEN(YourFieldName)=12, NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4)))))

The FIND Function:  (online help)
The FIND function is similar to the SEARCH function with two differences;
  • FIND is case sensitive.
  • FIND does not support wild cards.

Examples Using Dates

You can create rules to limit date ranges by using the TODAY() function or the DATEVALUE() function.
Date must be in the future:
Date must be in the future by "x" days:
    =YourFieldName>TODAY() + 3I.e. If today is the 7th, then valid dates start on the 11th.
Test against a particular date:  (online help for DATEVALUE)
Date must be between now and the end of the current year:  (online help for YEAR)
    =YourFieldName < DATEVALUE( "12/31/" & YEAR(TODAY()) )This example calculates a DATEVALUE by building a string to represent a future date.
Date must be within the next 30 days:
    =AND(YourFieldName >= TODAY(),YourFieldName <= TODAY()+30)
Date must be a Monday:   (1 = Sunday, 2 = Monday, 3 = Tuesday, …)   (online help for WEEKDAY)
Date must be the last day of the month:
Date must be the first day of the month:
Note: Some of the more "fun" Excel date functions like WEEKNUM, NETWORKDAYS and EOMONTH are not supported in SharePoint.

Not so useful tests!   Smile

Value must be greater than PI.  (3.14159265358979 more or less…)
    =YourFieldName > PI()
And some square roots:
    =YourFieldName > SQRT(2)
And of course you need a little trig:
    =TAN(RADIANS(YourFieldName)) > 1



paul.maksimovic@connectassist.co.uk said...


Could you help me please?
I am trying to write a validation that will accept either a blank value or the first letter in capitals.
So far I have or(title="",CODE(UPPER(MID(title,1,1)))=CODE(MID(title,1,1))
I have tried everything but cannot get it to work.
many kind Regards

Mike Smith said...


Excel's OR does not "short circuit", meaning that if the cell is empty, the second part of the OR is still tested, and the CODE function returns an error on the empty cell.

Try this:

If the IF condition is true, the false part is not executed, no error on any empty CODE.


Karen Jones said...

we have a reservation form where we want to ensure the check out date is greater than the check in date. They are both Date and Time Type and required. I tried the validation =Check Out Date>Check In Date

Since the check in date can be any date, TODAY won't work in the formula. Any suggestions?

Mike Smith said...


What you have should work. As there are spaces in the names you will need to add square brackets:

[Check Out Date] > [Check In Date]

Karen Jones said...

I'm getting the error message "The formula cannot refer to another column."

Mike Smith said...


You will need to add that as a Item Validation formula instead of a Column Validation. See Validation Settings inside of the List's Settings page.


Inga Bruce said...

HI Mike - I'm creating site columns which are part of a site content type, as I want to attach these fields to lists in multiple subsites and have consistency over the calculated formula.

The formula are date creation ones e.g. First Milestone Date + Expected Duration (+handling if weekend) = Next Milestone Date (which is then used to create subsequent Milestones)

And sums of costs, sums of prices, difference between prices and costs, and margin calc.

I'm getting error:
"The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column. "

The column most definitely does exist!


Mike Smith said...


Have you renamed the BL_Cust_Release somewhere along the way? Go to List Settings and click the column name. Check the field name at the end of the URL of the Edit Column page. While looking at the URL also check to see if the special characters like the underlines are represented with the correct code. For example "_" is "%5F".


Sandra Boyd said...

I have two fields, a Month Ending Date and a Receipt Date. I want data validation on the Receipt Date that it must be in the same month as the Month Ending Date and less than or equal to Today. Do you have any suggestions?

Mike Smith said...


As your request accesses multiple columns, you will need to use the List Validation feature. (LIST (or LIBRARY) Settings, Validation Settings)

=AND( MONTH(MonthEndingDate)=MONTH(ReceiptDate), ReceiptDate<=TODAY() )

Of course, change to your column names...


Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.