SharePoint list validation: end date after start date, and one of two fields

Q: How do I write a validation formula with IF, AND and OR?

Microsoft's formula page shows IF([Column1]<=[Column2], "OK", "Not OK"), AND and OR with comparisons. For validation, end the formula in true or false instead of text.

Q: How do I validate an email or a code pattern in a column?

For an email, =ISNUMBER(FIND("@",[Contact email])) checks that an at sign is present. It does not check that the address is real. For a code pattern, build it from LEN, LEFT and FIND and test it.

Q: Why does SharePoint say the formula must reference only this column?

A formula that points at another column was entered in column validation. A Microsoft Q&A answer says to move it to the list's Validation settings.

Q: Can I show different messages for different rules?

Not in list validation. It has one User message for the whole list. Write one message that names each problem, or use a Power Apps form if you need separate messages.

Q: Will validation fix rows that are already in the list?

No. Our advice, not a Microsoft rule: it checks a row when it is added or edited. Old rows that break the rule stay until someone edits them.

Short answer: open List settings, then Validation settings, and enter a formula that is true for a valid row, for example =[End date]>=[Start date], plus a message the person sees when it fails. A rule about one column goes in that column's Column Validation section. A rule that compares columns goes in the list's Validation settings.

Why bad data gets in

A list lets people type anything. After a few weeks you see leave requests that end before they start. You see contact rows with no phone and no email, so nobody can reach the person. Each bad row costs someone a call or an email to fix.

A validation rule stops the row at the door. The person sees your message, fixes the entry and saves again. Nobody has to chase them later.

Two places for a rule

Microsoft's help page on columns describes a Column Validation section in the column settings. You enter a formula there and add a User message that says what valid data looks like. Microsoft's page says the section is "not available for all types of columns".

A column rule can only look at its own column. If you type a formula that points at another column, SharePoint answers with a message like "Check the formula for spelling mistakes or update the formula to reference only this column." A Microsoft Q&A answer says the fix is list validation: in the list settings, under General Settings, open Validation settings, add the formula and save. Exact names may differ on your screen.

So the choice is simple:

One column only (for example, text must be 7 characters): column validation.
Two or more columns (for example, end after start): list validation.

How the formula works

A validation formula must come out true for the row to save. If it comes out false, SharePoint shows your user message and does not save. Microsoft's column page points to its page of common formulas for the formula syntax.

Microsoft's page of common formulas shows these building blocks:

A comparison, such as IF([Column1]<=[Column2], "OK", "Not OK").
A blank check, such as IF(ISBLANK([Column1]), "Not OK", "OK").

A validation formula uses the same pieces, but it ends in true or false instead of text. The formula uses the column names inside square brackets. If a formula fails with an odd error, check the spelling of the column name first. Our advice, not a Microsoft rule: regional settings can change the separator, so a comma may need to be a semicolon. Test every formula.

Copy-paste formulas

Each line is a list validation formula that must come out true for a valid row. Replace the names in square brackets with your own column names. The comparison, IF, ISBLANK, LEN, FIND and ISNUMBER pieces come from Microsoft's page of common formulas. The finished rules are my own combinations, so test each one.

Date order: =[End date]>=[Start date]
Number range: =AND([Quantity]>=1,[Quantity]<=100)
Text length, exactly 7 characters: =LEN([Code])=7
Not blank: =NOT(ISBLANK([Contact phone]))
Email contains @: =ISNUMBER(FIND("@",[Contact email])) If the email is optional, wrap it: =IF(ISBLANK([Contact email]),TRUE,ISNUMBER(FIND("@",[Contact email])))
Code contains BD: =ISNUMBER(FIND("BD",[Code]))
Code starts with BD: =LEFT([Code],2)="BD"
Phone or email: =OR(NOT(ISBLANK([Contact phone])),NOT(ISBLANK([Contact email])))
Required only if Status is 5 Done: =IF([Status]="5 Done",NOT(ISBLANK([Reason])),TRUE)
Either of two conditions: =OR([Quantity]>[Minimum],[Override]="Yes") (Override is a Choice column with Yes and No.)

Use these as starting points. A code pattern such as two letters then four digits has no single ready formula on Microsoft's page, so build it from LEN, LEFT and the other text functions that page lists, and test it.

A worked example: a leave request list

This example uses a leave request list with these columns:

Request summary (Single line of text)
Employee (Person)
Start date (Date)
End date (Date)
Contact phone (Single line of text)
Contact email (Single line of text)
Status (Choice, with values 1 New, 2 In review, 3 Rejected, 4 Approved, 5 Done)

Our advice, not a Microsoft rule: put a number in front of each Choice value so you can keep the order in your head and type each value the same way every time.

If you have not built a list yet, see "Replace a spreadsheet tracker with a SharePoint list". This guide only adds the rules.

Rule 1. End date must not be before start date

Open the list. Choose the gear icon, then List settings. Exact names may differ.
Under General Settings, choose Validation settings.
In the Formula box, enter:

=[End date]>=[Start date]

In the User message box, write something clear: "End date must be the same day as the start date or later."
Select Save.

The date columns are matched by name, so use the names exactly as they appear in your list. Microsoft Q&A shows this same comparison written with [End Date] and [Start Date] in a thread where it was rejected as a column rule and then moved to list validation.

If the dates are optional, the rule above can block a row with a blank date. One way to start is a version that lets blanks through. This is built from the pieces on Microsoft's formula page, so test it:

=IF(ISBLANK([Start date]),TRUE,IF(ISBLANK([End date]),TRUE,[End date]>=[Start date]))

Read it from the inside out. If the start date is blank, pass. If the end date is blank, pass. Otherwise the end must be on or after the start. The exact formula may differ for your list. Test it.

Rule 2. One of two fields must be filled in

A contact row needs a way to reach the person. Either a phone or an email is fine, but not neither. This is a list rule, because it looks at two columns.

Open Validation settings as above.
Enter this formula in the Formula box:

=IF(ISBLANK([Contact phone]),IF(ISBLANK([Contact email]),FALSE,TRUE),TRUE)

In the User message box, write: "Enter a phone number or an email address."
Select Save.

Read it like this. If the phone is blank, look at the email. If the email is also blank, the result is false and the row does not save. If the phone is filled in, the row passes. The same pattern works for any pair of columns. The exact formula may differ for your columns. Test it.

Only one list rule box

Validation settings has one Formula box and one User message box for the whole list. If you have two rules, you can join them in one formula, but the person will see the same single message for both. Write a message that names both problems: "Check that the end date is not before the start date, and that a phone or email is entered." Keep the number of list rules small. One or two is easier to fix later than six.

Test before you tell the team

Make a test row with an end date before the start date. It should not save and should show your message.
Fix the date and save. It should save.
Make a test row with no phone and no email. It should not save.
Add only a phone. It should save.
Add the same rows through the new-item form, a grid edit and an import, if your team uses all three. Check that each one behaves as you expect.

Our advice, not a Microsoft rule: expect validation to run when a row is added or edited, so old rows that already break the rule stay as they are until someone edits them.

What to avoid

Do not put a two-column formula in column validation. SharePoint will reject it.
Do not build a rule around today's date, like "must be after today". Our advice, not a Microsoft rule: the rule can run again when someone edits the row, so an old row can be blocked later for no clear reason.
Do not write a vague message. "Invalid" tells nobody what to fix. Say what is wrong and what to do.
Do not stack several rules in one formula. If you cannot read it in one go, it is too long to fix.
Do not treat a rule as security. Validation checks data. It does not control who can see or change a row.
Do not retype a Choice value from memory in a formula. Copy it, including the number.

When a rule depends on another column's value

A rule like "the reason is required when the status is 3 Rejected" is a different case. It is covered in "Make a SharePoint column required only when another column has a value".

To show or hide a field instead of blocking a save, see "Make the new-item form shorter: sections and show-if rules".

When to use Power Apps instead

Stay with list validation if the rule is a simple yes or no check with one message. Move to a Power Apps custom form only if you need a different message for each rule, a different control, or checks against another list. Every custom form needs an owner to look after it. For the full comparison, see "Power Apps or a SharePoint list form: which one do you need?".

Honest limits

Validation catches typing mistakes. It does not catch wrong but valid answers, such as a real date that is the wrong date. Microsoft's column page says the Column Validation section is not available for all types of columns, so some columns will need another approach. The single message for the whole list is a real limit when you have several rules. Exact names may differ on your screen.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

SharePoint column validation formula examples?

Date order is =[End date]>=[Start date], a number range is =AND([Quantity]>=1,[Quantity]<=100), and a blank check is =NOT(ISBLANK([Contact phone])). See the copy-paste list above.

How do I write a validation formula with IF, AND and OR?