Skip to main content
Share Your Experience: Take the 2024 Developer Survey
6 votes
Accepted

Column validation on a non required field

Try using below formula: =IF(ISBLANK([column 1]), true, IF(LEN([column 1])=19, true, false) This will allow users to add either blank values or the text with length of exact 19 characters.
Ganesh Sanap - MVP's user avatar
3 votes
Accepted

Single line of text - Numerical characters only

Two solutions come to mind... Check for the problem characters: =AND( ISNUMBER(0+[Invoice Number]), ISERR(FIND("$",[Invoice Number])), ISERR(FIND(".",[Invoice Number]...
Mike Smith - MCT's user avatar
2 votes

SharePoint list validation code not working

You need to set the formula value to TRUE in case user selects the "No-Copy". So, try using below formula: =IF([TAtestColumnChoose]="Copy",IF(ISBLANK([Description]),FALSE,TRUE),TRUE) Note: ...
Ganesh Sanap - MVP's user avatar
2 votes

Column Validation only when item has a value

Working forumla: =IF(AND(NOT(ISBLANK([My Text Field])),NOT(LEN([My Text Field])=3)),FALSE,TRUE)
user3587336's user avatar
2 votes
Accepted

how to set the minimum length of a single line text in sharepoint online?

SharePoint OOTB Solution: Use Column Validation: Go to your list settings. Click on your text column. Move to column validation section and enter below expression: =LEN([your column name])>24 ...
Ganesh Sanap - MVP's user avatar
2 votes

Restrict User's Choice in Datepicker

Hmm, that's a good question. I had a look around for 10 mins and didn't find something that matches up with what you want, but I did find something you could try to tinker with. @Mike Smith, a well ...
Tally's user avatar
  • 2,243
2 votes
Accepted

How to have validation for calculated column

You can add the same formula in list validation settings to show an error and don't allow users to save the form: Follow below steps: Go to You SharePoint list. Then go to List settings. Click on ...
Ganesh Sanap - MVP's user avatar
2 votes
Accepted

Text Column Validation: Numbers only, 5 numbers, a period, and 3 numbers

Try this: =IF(LEN([YourColumn])=9,IF(MID([YourColumn],6,1)=".",IF(ISNUMBER(VALUE((MID([YourColumn],1,5)))),ISNUMBER(VALUE((MID([YourColumn],7,3)))),FALSE),FALSE),FALSE) First it validates ...
UBK's user avatar
  • 1,213
2 votes
Accepted

Need help with column validation on a single line of text field

Use this formula for column validation: =OR(LEFT([Ticket Number],3)="inc",LEFT([Ticket Number],6)="sctask",LEFT([Ticket Number],3)="prb",LEFT([Ticket Number],3)="chg&...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

SharePoint Calendar Validation - Time Limit

Added the condition to this formula: ([End Time] is the same day as [Start Time] and allowed limit is 0 hour 15 minutes) =AND(AND(CHOOSE(WEEKDAY([Start Time]),FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE),...
Matiur Rahman's user avatar
1 vote
Accepted

Complex SharePoint Calendar Validation

Try the below: Assuming [Start Time] at 12:30, 1:15 or 1:30 PM =AND(CHOOSE(WEEKDAY([Start Time]),FALSE,TRUE,FALSE,TRUE,FALSE,FALSE,FALSE),IF(OR(TEXT([Start Time],"hh:mm")="12:30",...
Matiur Rahman's user avatar
1 vote

Validation Upper case not working correctly

Looks like there is one additional ) in your 2nd formula. It should be like: =AND(LEN(Route)=7,MID(Route,4,1)="-",CODE(UPPER(MID(Route,1,1)))=CODE(MID(Route,1,1)),CODE(UPPER(MID(Route,2,1)))=...
Ganesh Sanap - MVP's user avatar
1 vote

How to insert multiple values into one column separated by semicolumn

I don't think you can validate this column using calculated formula to meet the requirements. Because, the length of the column value is dynamic, and none of the followings could be validated ...
Matiur Rahman's user avatar
1 vote

column validation in a SharePoint Online List based on the value of multiple

Don't use IF() in the validation formula. Use a formula that results in TRUE or FALSE, but not with IF(). You may want to test each component first before you plug them all together into one big ...
teylyn's user avatar
  • 8,949
1 vote

How do I write validation statement to require a day of the week?

If you want your users to enter only future Monday's then try using below formula: =IF(AND(TEXT(WEEKDAY([Date Column]), "dddd") = "Monday", [Date Column] > Today()), true, false)...
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

Validation setting for Date Sharepoint

Try using this formula: =OR([ActionDate] > DATE(YEAR(Today())+2,MONTH(Today())+6,DAY(Today())),ISBLANK([ActionDate])) Validate Due Date less than 12 months
Ganesh Sanap - MVP's user avatar
1 vote
Accepted

How to split a column by delimiter

We could use flow in SharePoint online to accomplish your requirement: My test List: My flow: Compose function as shown below: split(triggerBody()?['ColumnA'],'-')?[0] split(triggerBody()?['...
Julie_MSFT's user avatar
  • 4,227
1 vote
Accepted

How to validate decimal value

Try using below formula in column validation: =IF([Number Column] = ROUND([Number Column], 0), true, false) Note: Sometimes comma(,) does not work in formula (I am not sure but it is based on ...
Ganesh Sanap - MVP's user avatar
1 vote

How to validate decimal value

You could add this to your column validation(change the number to your column name). =number=ROUND(number,0) Test result:
Amos's user avatar
  • 1,981
1 vote
Accepted

Date must be more than 2 Years from today

Try something like below: =[Date Column] > DATE(YEAR(Today())+2,MONTH(Today()),DAY(Today())) Read more about column validations in SharePoint at: SharePoint Online List Column Validation ...
Ganesh Sanap - MVP's user avatar
1 vote

Validation on column between 2 values

SharePoint Column Validation Examples for your reference: https://techtrainingnotes.blogspot.com/2015/10/sharepoint-column-validation-examples.html
Lisa Chen MSFT's user avatar
1 vote

Column validation sharepoint list.Whats wrong with this formula

Use =[Review Date]>TODAY() in column validation. Tested for Date after Today's date. it is allowing to submit item. Result:
Anuja's user avatar
  • 3,097
1 vote
Accepted

Individual column Validation Based Off The Previous Date Columns

If you are using list validation, which works in both classic and modern experience on SharePoint Online, then this is the formula you want: =AND(AND([Date Closed]>=[O365 Queue],[Date Closed]>=[...
Mihail's user avatar
  • 2,079
1 vote
Accepted

How to restrict users to upload .pdf and .zip only in Modern SharePoint Online Site (Document Library)

This is not possible in SharePoint Online using OOTB capabilities of SharePoint. However there are some workaround to achieve this: Create an Event receiver to check the file type before uploading ...
Ganesh Sanap - MVP's user avatar
1 vote

Add more than one formula in SharePoint Column validation

You can use AND or OR to add two formulas together, choose one as your requirement. If you are not sure, you can provide the two detail formulas and requirement.
Kally_MSFT's user avatar
  • 3,034
1 vote

Add more than one formula in SharePoint Column validation

Without knowing the formulas (maybe there are problems in them) I can only suggest to put them in brackets. Following your example, it should be: =(formula 1) AND (formula 2)
MP_89's user avatar
  • 149
1 vote

Add more than one formula in SharePoint Column validation

It has to be in below format for logical operations AND and OR Column 1 - 15 Column 2 - 9 Column 3 - 8 =AND([Column1]>[Column2], [Column1]<[Column3]) - Is 15 greater than 9 and less than ...
Siddharth Vaghasia's user avatar
1 vote

Date validation to specific year for list Column in view

You need change the validation formula to this: =TEXT([Data documento],"yyyy")="2018" Try to use this in list validation. Go to list settings-> validation settings:
Michael Han's user avatar
  • 5,201
1 vote
Accepted

Column Validation check passes on Create but fails on Edit

Per my test, I can reproduce your issue in my environment. You could try this workaround: 1.Create a calculated column, and use this formula : =[Start Month]-[Created] 2.Change the list validation ...
Michael Han's user avatar
  • 5,201
1 vote

SharePoint 2013 - Validate Date Columns that allow for blanks

I needed clarification, but if my assumptions/interpretation was correct I think this should work: =AND( DATEDIF( [Permit Start Date], [Permit End Date], "d" )>=0, ...
KGlasier's user avatar
  • 1,745

Only top scored, non community-wiki answers of a minimum length are eligible