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?
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?
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!
See Also, SharePoint Designer Workflow check if the date field is empty
