SharePoint Column Validation Examples

Now available on Amazon!

Over 100 Examples!

A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

• Over 60 Calculated Columns examples
• Over 30 Column Validation examples
• 11 List/Library Item Validation examples
  (and every one of the Column Validation examples can be used here.)
• 7 Calculated Column Default Values examples
• 15 Workflow “workarounds” for things SharePoint formulas can’t do

Update 11/18/2017… added test for nearest 1/4th, 1/10th, etc.
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, 2013, 2016 and SharePoint Online/Office 365.

 

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 names 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(YourFieldName="A",YourFieldName="C",YourFieldName="E")

=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)

Examples:

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)

=NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4))))

   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 for Numbers

Validate if a number ends in either .25, .50 or .5 or .75 or is a whole number.

     =ROUND([Activity Effort]*4,0)/4 = [Activity Effort]

If you wanted the number to the nearest 10th then divide by 10, round then multiple by 10.
     =ROUND([Activity Effort]*10,0)/10 = [Activity Effort]
This works all of the time for numbers with non-repeating digits. I.e. It will not work for 1/3 as 0.333333333333... can't be truly represented as a fixed set of digits. (It will actually work for 1/3 if you know the right number of digits to type! it looks like it's 15 significant digits: 0.333333333333333, 0.666666666666667 and 1.33333333333333 will work for 1/3, 2/3 and 1 1/3 with =ROUND([Activity Effort]*3,0)/3 = [Activity Effort] as the validation.)

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:
    =YourFieldName>TODAY()

Date must be in the future by "x" days:
    =YourFieldName>TODAY() + 3
I.e. If today is the 7th, then valid dates start on the 11th.

Test against a particular date:  (online help for DATEVALUE)
    =YourFieldName>datevalue("1/1/2015")

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)
    =WEEKDAY(YourFieldName)=2

Date must be the last day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn)+1,0)

Date must be the first day of the month:
=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),1)

Note: Some of the more "fun" Excel date functions like WEEKNUM, NETWORKDAYS and EOMONTH are not supported in SharePoint.

Not so useful tests!  

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

.