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

.

44 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 J 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 J 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

Unknown 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

Unknown 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

Unknown 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

Unknown said...

Hi Mike, excellent! It did work for me - I shared this knowledge with the other Fordies ;-) so we can use it within our Company.

Thank you!! :)

Anonymous said...

I'd like to validate if a number ends in either .25, .50 or .5 or .75 or is a whole number or a combination of a whole number and the aforementioned decimals. This is for tracking activity in increments. This is what I have but it isn't working 100% =OR(RIGHT([Activity Effort],2)="25",RIGHT([Activity Effort],1)="5",RIGHT([Activity Effort],2)="75",RIGHT([Activity Effort],1)="",MOD([Activity Effort],1)=0)

Mike Smith said...

Anonymous,
> validate if a number ends in either .25, .50 or .5 or .75 or is a whole number

The is relatively simple if the column is Numeric.
=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.)

Mike

Unknown said...

Looking for some help with a limit formula. I would like to limit the number of entries to any given day based on a data point / column. we have three different columns that employees can chose from. I would like to limit 10 of one column, 6 of another and 2 of the last on any one given day.

Thank you for any insight!

Unknown said...

Looking some help with a column validation formula which would only allow 15 calendar entries on any given day.

Mike Smith said...

Kdn0184,

> looking for some help with a limit formula. I would like to limit the number of entries to any given day based on a data point / column.

"Entries" as in rows in a list? And, where is the data point? Another list?

In general, column validations can only look at the single value in a column.

What you are describing may require a workflow or an Event Receiver.

Mike

Mike Smith said...

Kdn0184,

> only allow 15 calendar entries on any given day

This cannot be done with a validation formula as it needs to look at other items/rows in the list. This could be done with a workflow or an Event Receiver. Let me know if you would like to see an example of a workflow solution.

Mike

Unknown said...

I have list field that needs validation set so the input value is always a multiple of 30. Think of a timesheet where records are collected in 30 minute increments. I want the field validated so it only accepts 30,60,90, etc. I don't need an upper boundary but minimum is 30 (no zero entries)

Mike Smith said...

Chris,

At least two solutions:

=AND(somenumber>0,ROUND(somenumber/30,0)*30=somenumber)

=AND(somenumber>0,INT(somenumber/30)*30=somenumber)

Unknown said...

@ Mike Smith

I tried both formulas and they both return errors that say

" The formula cannot refer to another column. Check the formula for spelling mistakes or update the formula to reference only this column.
"

Obviously there is not actually a reference to another column, so not sure what is actually causing the error.

Mike Smith said...

Chris,

Did you replace "somenumber" with the name of your column?

Mike

Carmen said...

Hello,

Looking for some help on validating a checkbox and a date column. I'm wondering if it's possible to validate this: A date can be added only if the coordinating checkboxes is checked (ex: Test1 Test2, Test3). If not checked, then display an error message and continue to leave the date field blank.

Thank You,
Carmen

Mike Smith said...

Carmen,

Sorry for the late response...

Something like this should work:

Add a List Validation (List Settings, Validation Settings).

First test: The list item is valid if the checkbox is cleared (NO) and the date column is blank.
=AND( ISBLANK([yourDateColumn]), NOT([yourCheckBoxColumn]) )

Second test: Anything is valid if your check is checked (YES).
=[yourCheckBoxColumn]

Combined:
=OR( AND(ISBLANK([yourDateColumn]),NOT([yourCheckBoxColumn])), [yourCheckBoxColumn])

Elmo said...

Hi Mike
I'm have a date field in a custom list which Id like the font to turn bold and red if the date is in the past.
Can you help?

Mike Smith said...

Elmo,

You can't do that with a validation formula. There are several solutions on the web depending on your SharePoint version (2010,2013,2016, SharePoint Online, etc.) and where you want the change displayed (list view, Quick Edit, the New/Edit form).

Mike

Sam said...

Hi Mike, I have a Single line of text field in a SharePoint Online list where the data needs to be entered in the following format - NLLLNNNNN (N = Number and L = Letter). Can you help please?

Mike Smith said...

Sam,

This should work:

=AND(
LEN(yourColumnName)=9,
ISNUMBER(VALUE(MID(yourColumnName,1,1))),
NOT(ISNUMBER(VALUE(MID(yourColumnName,2,3)))),
ISNUMBER(VALUE(MID(yourColumnName,5,5))),
AND( MID(yourColumnName,2,1)>="A", MID(yourColumnName,2,1)<="Z" ),
AND( MID(yourColumnName,3,1)>="A", MID(yourColumnName,3,1)<="Z" ),
AND( MID(yourColumnName,4,1)>="A", MID(yourColumnName,4,1)<="Z" )
)

Note that 1aaa11111 and 1AAA11111 are both accepted.

Mike

Elvis S said...

Hi Mike,
This is really informative. Thank you.

Is there a way to validate for TRIM() and PROPER() on SharePoint? I'm looking to reduce extra spaces and ensure proper capitalization. Thanks

Mike Smith said...

Elvis S,

If you just want to verify that there are no extra spaces then just use this as the validation formula:

=TRIM( MyColumnName ) = MyColumnName

PROPER is not as easy as SharePoint text comparisons are not case sensitive. The EXACT function does case sensitive comparisons.

=EXACT( PROPER( MyColumnName ), MyColumnName)

Mike

Elvis S said...

Thanks Mike, both work like a charm. Any chance I can merge the two to do both? Trim spaces and check for accurate capitalization?

Mike Smith said...

Elvis S,

Sure, just combine them with AND. (Standard Excel functions)

=AND( TRIM( MyColumnName ) = MyColumnName, EXACT( PROPER( MyColumnName ), MyColumnName)
)

Mike

Elvis S said...

Thanks Mike.

I've also been trying to validate entries in upper case and no spaces. While the TRIM validation spaces works, I cannot for the life of me get UPPER to work. What am I doing wrong?

=AND(TRIM([MyColumnName])=[MyColumnName],UPPER([MyColumnName])=[MyColumnName])

Mike Smith said...

Elvis S,

Use Exact():

=AND(TRIM([MyColumnName])=[MyColumnName], EXACT( UPPER([MyColumnName]), [MyColumnName] ) )

Mike

Unknown said...

I need help with a formula I want to force users to enter two fields(they cannot leave it blank) or they cannot move on. This is Validation settings in SharePoint.
This is what I have but it is not working:

=IF(AND(NOT(ISBLANK(Vendor code)),ISBLANK([Date Completed]),Vendor code<>"",TRUE,FALSE),TRUE)

Mike Smith said...

Unknown,

Your AND has a hard coded FALSE, so will never be TRUE.

AND(
NOT(ISBLANK(Vendor code)),
ISBLANK([Date Completed]),
Vendor code<>"",
TRUE,
FALSE
)

Maybe you wanted something like this: (The IF is not needed as ADD returns TRUE or FALSE)

=AND( NOT(ISBLANK(Vendor code)), ISBLANK([Date Completed]), Vendor code<>"" )

Also ISBLANK may not work as expected, depending on the column data types. (I have a section in my Calculated Columns book in testing for "blank" columns!)

Anonymous said...

I am trying to figure out a validation for SharePoint- I have 5 content types using a look-up to a list for the 5 different outcome lists. I have a status column with "In Progress", "Deferred" and "Completed" I only want the user to be able to choose an outcome and an outcome date when the status is completed. In turn I want to force the user to have to choose an outcome and outcome date when they status is "Completed"

I have tried this but SharePoint doesn't accept it and it doesn't speak to "Deferred" status:

- IF([STATUS]="In Progress",AND(ISBLANK(ETOutcome),ISBLANK(STOutcome),ISBLANK(TTOutcome),ISBLANK(TCOutcome),ISBLANK(MTOutcome),ISBLANK([Outcome Date])),
IF(OR([STATUS]="Approved",[STATUS]=""),AND(NOT(ISBLANK(ETOutcome)),NOT(ISBLANK(STOutcome)),NOT(ISBLANK(TTOutcome)),NOT(ISBLANK(TCOutcome)),NOT(ISBLANK(MTOutcome)),NOT(ISBLANK([Outcome Date]))))

Mike Smith said...

Anonymous,

Lets take the first issue, "SharePoint doesn't accept it". You are missing a paren.

The best way to sort these out is to reformat the formula with multiple lines and indents. (You can actually paste nicely formatted formulas into SharePoint, but when you look at it again, it will be reformatted as "ugly".)

As blogger strips out leading spaces, I've added underlines here:

IF( [STATUS]="In Progress", <--test
<- if true ->
___AND( ISBLANK(ETOutcome),
________ISBLANK(STOutcome),
________ISBLANK(TTOutcome),
________ISBLANK(TCOutcome),
________ISBLANK(MTOutcome),
________ISBLANK([Outcome Date])
______),
<- if false ->
___IF( OR([STATUS]="Approved",[STATUS]=""), <--test
_____AND( NOT(ISBLANK(ETOutcome)), <-- if true
__________NOT(ISBLANK(STOutcome)),
__________NOT(ISBLANK(TTOutcome)),
__________NOT(ISBLANK(TCOutcome)),
__________NOT(ISBLANK(MTOutcome)),
__________NOT(ISBLANK([Outcome Date]))
________)
<-- no if-false for the nested if
_____) <-- end of nested if

<- Missing closing paren ")" for the first IF ->

Let me know if that works for you and then I will try to duplicate your issue.

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.