I am stuck on a validation column, I need Date Effective From mandatory if Approval Status is equal to Approved and vice versa I need Approval Status mandatory if Date Effective From is not blank.

I have the below code which works if status is approved and no date is entered.

=IF([Approval Status]="Approved", AND([Date Effective From]<>""), TRUE)

How do I go adding also the condition vice versa that if the users enters the date the status must be changed to Approved?

3 Answers 3


You cannot set the Status to "Approved" using formula, But you can mandate users to select Approved status using below formula:

=IF([Approval Status] = "Approved", IF([Date Effective From] <> "", TRUE, FALSE), IF([Date Effective From] <> "", FALSE, TRUE))

Additionally, you can show error message to users as per your requirements (Something like: "Status should be Approved if Effective date is Entered OR Enter Effective date if Status is Approved").


The column validation only affects the column it belongs to. Would you like to provide examples about your further plan?

If Momahed gets you right, then you will need to use tools like workflow/flow to update the value of Approval status column. Column validation does not support updating fields. It only set rules on the value user fill in.

Or are you trying to create a new validation rule for the approval status column?

  • I am trying to achieve this via the Validation Settings, basically if the users edit the drop down column to approved they must enter a date in the effective columns and this works perfectly with the validation settings I have entered, What I also want is that if the users enter a date in the effective column and they forget to change the status to approved then the validation rule kicks is as for first case. Jan 7, 2021 at 10:39

If the users enter the date the status must be changed to Approved?

You can't set a field value using the validation formula. Instead, you should use Workflow that runs on Item Update and Add, to update the list item if the date value is not null!

enter image description here

enter image description here

See Also, SharePoint Designer Workflow check if the date field is empty

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.