I have an Action Items list that captures [Created], [Status], and [Date Completed] columns. [Date Completed] is an optional column to be entered at a later date when the Action Item is closed. If [status]="Completed", [Date Completed] must be greater than or equal to [Created]. If [status]=blank, no validation is needed and [Date Completed] can be left blank. How do I code this formula? I have tried to adapt several similar formulas posted on this site without success. In all cases, SharePoint displays an error that [Date Completed] must be > Created no matter what value is selected in [Status].

  • It almost sounds like you would be better off using a workflow than trying to mess with validated columns.
    – Matt
    Commented Aug 27, 2013 at 18:14

1 Answer 1


Use list validation for this. Make the column [Date Completed] a non required field. Then go to

List Settings -> Validation settings

Try this:

=OR(AND([Status]="Completed", [Date Completed]>=[Created]), [Status]="None")

Make your message something about how Date completed needs to be filled in if the item is completed.

  • 1
    Just a not on this. The formula could be simplified as: =OR([Status]<>"Completed", [Date Completed]>=[Created]) Commented Jun 1, 2015 at 5:42

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.