SharePoint Conditional Validation

Question

I Have Status & Completion date column in SharePoint list. I need to validate the Actual End Date on below conditions:

1. If status is "Completed", Actual End date cant be blank.
2. If status is not "Completed", Actual End date will be blank. No Date will be accepted

Answer 1

You can achieve it using List Validation functionality in SharePoint:

Below are the steps to configure List Validation:

• Go to SharePoint list
• Click on Settings icon and select List settings
• Select Validation settings
• Add formula & user message and click Save

You can add below formula:

=IF(AND([Status]="Completed",ISBLANK([Actual End date])),FALSE,IF(AND(NOT([Status]="Completed"),NOT(ISBLANK([Actual End date]))),FALSE,IF(AND(ISBLANK([Status]),NOT(ISBLANK([Actual End date]))),FALSE,TRUE)))

You can set validation message according your requirement

Above formula will handle following scenarios:

1. It will not allow to set Actual End Date when Status is not Completed or it's empty
2. It will now allow to blank Actual End Date when Status is Completed

See below output

Answer 2

In addition to above answer by @Kalpesh, you can also use the list validation formula like given below for your requirements:

=IF([Status]="Completed",IF(ISBLANK([Actual End date]),FALSE,TRUE),IF(OR(NOT([Status]="Completed"),ISBLANK([Status])),IF(NOT(ISBLANK([Actual End date])),FALSE,TRUE),TRUE))

Note:

1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
2. Use correct display name of your SharePoint columns in above formula.
3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Official Documentations:

1. Calculated Field Formulas

Similar threads:

1. Validation Column Not Working
2. List validation if then syntax