How date can be dependent on status changing

Question

I developed a travel plan list on SharePoint. In the plan, for travel, there are 3 drop-down stages:

• Planned
• Completed
• Cancelled

But the issue is anyone can select the "Completed" status without planning a trip. Which is now became a massive issue.

By the way, I used a validation formula under validation settings and this logic is necessary:

=AND([Planned End Date]>=[Planned Start Date],[Actual End Date]>=[Actual Start Date])

Now, I need an idea to set a new logic.

1. How it is possible if someone chooses the "Completed" option the logic will check whether s/he is "Planned" or not? If the travel is not "Planned" in advance, the system will restrict him/her to save it.

2. Also is there a way, if someone plans a trip and selected a date for this trip, s/he will get an auto-generated email that you have to change your status to "Completed" if the planned date expired? Here JSON code will play a vital role if you have any idea.

Any ideas or suggestions on JSON coding or other option?

Answer 1

Try using this formula in List Validation settings formula:

=IF(AND([Status]="Completed",OR(ISBLANK([Planned Start Date]),ISBLANK([Planned End Date]))),FALSE,AND([Planned End Date]>=[Planned Start Date],[Actual End Date]>=[Actual Start Date]))

---

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].

Similar threads:

1. Two formulas in validation settings
2. List validation to make multiple fields mandatory if dropdown meets condition