10/08/2015

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(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 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!   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

.

17 comments:

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

Hello

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...

Paul,

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(title="",TRUE,CODE(UPPER(MID(title,1,1)))=CODE(MID(title,1,1)))

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

Mike

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...

Karen,

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...

karen,

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.

Mike

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!

=IF((WEEKDAY(BL_Cust_Release,3)+1)>4,2,0)+BL_Cust_Release+28+1

Mike Smith said...

Inga,

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".

Mike

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...

Sandra,

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...

Mike

Rafał Just said...

Hello, I have a problem with my SP2010. I have been trying to implement a list validation with multiple conditions.

I would like to have query closed only if the following fields:

Issue status: If Resolved or Closed chosen, Update has to be set to either Yes or No.

=OR(AND(IF([Issue Status]="Resolved",IF([Update]="Yes",TRUE,FALSE),TRUE)),AND(IF([Issue Status]="Resolved",IF([Update]="No",TRUE,FALSE),TRUE)))

The above query is working for Resolved but when I add additional two conditions ( Closed, Yes and No ) into it it does not work. There is no syntax issue.

=OR(AND(IF([Issue Status]="Resolved",IF([Update]="Yes",TRUE,FALSE),TRUE)),AND(IF([Issue Status]="Resolved",IF([Update]="No",TRUE,FALSE),TRUE)),AND(IF([Issue Status]="Closed",IF([Update]="Yes",TRUE,FALSE),TRUE)),AND(IF([Issue Status]="Closed",IF([Update]="No",TRUE,FALSE),TRUE))).

I am more than sure than I am missing something here.
Could anyone help me out please?
Thank you.

Mike Smith said...

Rafal,

Easiest ways to solve these:

1) Write them in Excel. Name two cells IssueStatus and Update. Write and test the formula in Excel. Take the tested and working formula and replace column names with spaces with the SharePoint format: replace "IssueStatus" with "[Issue Status]". Paste the result into SharePoint.

2) Write them in Notepad or editor and neatly format them so you can see the nesting.

Your first example can be reformatted as:

=OR(
AND(
IF(IssueStatus="Resolved",
IF(Update="Yes",
TRUE,
FALSE
),
TRUE
)
),
AND(
IF(IssueStatus="Resolved",
IF(Update="No",
TRUE,
FALSE
),
TRUE
)
)
)

You can then see that it returns true if IssueStatus is anything other than "Resolved", and I don't think that is what you wanted. (Let me know if otherwise.)

A simpler form might be:

=AND(
IssueStatus="Resolved",
OR(
Update="Yes",
Update="No"
)
)

As you can see, the IFs are not needed if the returned value is TRUE or FALSE. 5=5 is TRUE, so you don't need to write IF(5=5,TRUE,FALSE)

The simplified version returns FALSE if IssueStatus is anything but Resolved. It also returns FALSE if IssueStatus is Resolved, but Update is neither Yes or No.

To expand the above to include Resolved or Closed:

=AND(
OR(
IssueStatus="Resolved",
IssueStatus="Closed"
),
OR(
Update="Yes",
Update="No"
)
)

So SharePoint ready:

=AND(
OR(
[Issue Status]="Resolved",
[Issue Status]="Closed"
),
OR(
Update="Yes",
Update="No"
)
)

Let me know if I misunderstood your example.

Mike

Rafał Just said...

Hi Mike,
Thank you for your help! :)
tried yours and works until the moment when I select other options under IssueStatus ( I also have rejected and active. Having written this I think I still need IF and TRUE, FALSE Statement.

So under IssueStatus I have in total 4 options to choose ( Active, Resolved, Rejected and Closed ).
Under Update I have 3 to use ( Yes, No and Unassigned set as default ).

My approach was that if I want to either set IssueStatus to Closed or Resolved - user has to change Update status from Unassigned to either Yes or No. But if the item is Active or Rejected there is no need to change Update status at all ( User can but does not have to ).

Using your query it is working for Resolved and Closed as wanted but unfortunately it is working for Active and Rejceted as well - something I would like to avoid really :)

Mike Smith said...

> I also have rejected and active.
You didn't tell me that! :-)

We need to wrap it one more OR to include those two options. So status is either Active, Rejected, Resolved plus yes/no or Closed plus yes/no.

=OR(
IssueStatus="Active",
IssueStatus="Rejected",
AND(
OR(
IssueStatus="Resolved",
IssueStatus="Closed"
),
OR(
Update="Yes",
Update="No"
)
)
)

SharePoint ready:

=OR(
[Issue Status]="Active",
[Issue Status]="Rejected",
AND(
OR(
[Issue Status]="Resolved",
[Issue Status]="Closed"
),
OR(
Update="Yes",
Update="No"
)
)
)

Mike

Mike Smith said...

Blogger stole the spaces! (Not needed, but makes it easier to read.)

=OR(
  IssueStatus="Active",
  IssueStatus="Rejected",
  AND(
    OR(
      IssueStatus="Resolved",
      IssueStatus="Closed"
      ),
    OR(
      Update="Yes",
      Update="No"
      )
  )
)

SharePoint ready:

=OR(
  [Issue Status]="Active",
  [Issue Status]="Rejected",
  AND(
    OR(
      [Issue Status]="Resolved",
      [Issue Status]="Closed"
      ),
    OR(
      Update="Yes",
      Update="No"
      )
  )
)

Sue Brower said...

Just figured out this little gem to check for all uppercase
=NOT(ISERROR(FIND(UPPER(FieldName),FieldName)))

Mike Smith said...

Sue,

That's cool! I like short and sweet!

Mike

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.