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
  • Have you tried the answers below? Do they help you? If you have any questions about this issue, please update it here. Commented Jul 4, 2023 at 9:16

2 Answers 2


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

enter image description here

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

enter image description here

enter image description here

enter image description here


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


  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.