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(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:
ab
c Ab
C aX
c a6
c aB
cDEF
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 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() + 3I.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
.
)
