Date Validation in Excel: Rules, Formulas and Fixes

Date Validation in Excel: Rules, Formulas and Fixes

To validate dates in Excel, select the cells, open Data > Data Validation, set Allow to Date, pick a comparison such as between or greater than or equal to, and enter your limits. For rules the Date option can't express, such as weekdays only, switch Allow to Custom and write a formula. The rest of this guide gives you the exact settings, the formulas, and the reasons a rule sometimes seems to "not work."

TL;DR

• Use Data > Data Validation > Allow: Date for simple limits. Microsoft documents eight comparisons: between, not between, equal to, not equal to, greater than, less than, and the two "or equal to" versions.
• Use =TODAY() as a limit to block past or future dates. The limit updates as the workbook recalculates, but old entries are not rechecked.
• Use Allow: Custom with a formula such as =AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5) for weekdays only or multi-condition rules.
• Validation controls what is stored, not how it looks. A format like dd mmm yyyy does not turn text into a date.
• Pasted values can bypass validation, so audit existing data as well as guarding new entry.

Table of Contents

• What date validation actually does
• Set up a basic date rule in six steps
• Allow only today or future dates (and block future ones)
• Use cell references instead of typed dates
• Custom formulas for rules the Date option can't express
• How to check whether a value is a real date
• The locale trap: 03/04/2026
• Changing how the date looks (dd mmm yyyy and others)
• Automatic dates: TODAY versus a fixed stamp
• Why your validation rule is not working
• Perspective: validation is a gate, not an audit
• Practice these rules hands-on
• Date validation from code
• Quick recipes
• FAQ
• Sources

What date validation actually does

Data validation is a gate on a cell. When someone types a value, Excel checks it against your rule and either accepts it or shows an error alert. For dates, the rule compares the entry with one or two boundaries: a start date, an end date, or both.

This matters because a real Excel date is a number (a serial value) that happens to be displayed like a date. Text that merely looks like a date, such as "March 4th" typed into a text-formatted cell, is a different thing. A good rule makes sure the cell holds the number, not the lookalike.

It also helps to separate two jobs that people mix up:

• Acceptance: which values are permitted. That is data validation.
• Display: how permitted values look. That is number formatting.

Microsoft's guidance on formatting dates covers the second job. Setting a custom format will not enforce quality on its own, and it will not repair text dates.

Set up a basic date rule in six steps

The steps below follow the Microsoft Support procedure and the workflow it recommends.

• Select the whole input range first. If you validate only the first cell and copy it down later, you risk gaps. Select, for example, A2:A500.
• Open the dialog. Go to the Data tab and choose Data Validation.
• Set Allow to Date. Then choose a comparison in the Data box, such as between.
• Enter the limits. You can type fixed dates, point to cells, or use formulas.
• Decide about blanks. The Ignore blank box controls whether an empty cell is acceptable. For a required field, think carefully about it and test it.
• Add messages. On the Input Message tab, tell people what is expected. On the Error Alert tab, choose the Stop style and write an error message that states the rule in plain words.

A useful error message names the rule and the format: "Enter a date between Jan 1, 2026 and Dec 31, 2026, like 3/4/2026." A vague "Invalid entry" just makes people guess.

Which comparison to choose

You want	Data setting	Limit(s)
A date range, such as a fiscal year	between	Start and end dates
Nothing before a project start	greater than or equal to	The start date or a cell holding it
Nothing after a deadline	less than or equal to	The deadline
One specific date only	equal to	That date
Exclude a blackout period	not between	Blackout start and end

Allow only today or future dates (and block future ones)

This is the most common request, and Microsoft's own examples cover it. To allow only today or later, set Allow to Date, Data to greater than or equal to, and Start date to =TODAY(). To block future dates, such as a transaction date that can't be tomorrow, use less than or equal to with =TODAY().

For a rolling window, use between with a Start date of =TODAY() and an End date of =TODAY()+3. That is the example in the Microsoft documentation: it permits today through three days ahead. Change the 3 to whatever your business rule needs.

Two cautions:

• TODAY recalculates whenever the workbook does, so the rule moves with the calendar and you never edit the current date by hand.
• That is true only for new or edited entries. A date that was valid in March can become out of range in October, and validation will not flag it. Treat the rule as an entry gate, then audit existing records periodically, for instance with conditional formatting based on another cell.

If you want to practice how TODAY, NOW, DATE and DATEDIF behave before you build rules on them, our Excel date functions guide walks through each one.

Use cell references instead of typed dates

Hard-coded dates in a rule are easy to forget. When the fiscal year changes, nobody remembers which dialog holds the old limit.

A cleaner setup:

• Put the start date in one cell (say, Settings!B1) and the end date in another (Settings!B2), with labels.
• In the validation dialog, enter =Settings!$B$1 and =Settings!$B$2.
• Next year, change two cells and every rule updates.

The dollar signs matter. They lock the reference so it doesn't shift as the rule applies down the column. If that notation feels shaky, our guide to the absolute reference explains how to lock a cell.

When you need an exact limit inside a formula, build it with DATE(2026,3,4) rather than typing "3/4/2026." It is read the same way on every machine, which brings us to the biggest hidden trap.

Custom formulas for rules the Date option can't express

The built-in Date option handles ranges and comparisons. It can't say "weekdays only" or "must be in 2026 and not a Sunday." For those, set Allow to Custom and enter a formula that returns TRUE for acceptable entries. Write the formula as if for the first cell of your selected range, using a relative reference like A2.

Weekdays only

=AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5)

WEEKDAY(date,2) numbers Monday as 1 through Sunday as 7, so anything 5 or below is Monday to Friday. ISNUMBER keeps text-like values out. Both points come from the Microsoft guidance.

A date in a particular year

=AND(ISNUMBER(A2),YEAR(A2)=2026)

If the year lives in a cell, replace 2026 with an absolute reference such as $F$1.

Several conditions at once

Combine tests inside AND. For example, a weekday in 2026 that is not in the past:

=AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5,YEAR(A2)=2026,A2>=TODAY())

Test each condition separately before you combine them. If the combined rule rejects something you expect to pass, you will know which part to suspect.

A date or "N/A"

A frequent forum request is a cell that accepts a date or the text N/A. With Custom, an OR handles it:

=OR(ISNUMBER(A2),A2="N/A")

Note that this accepts any number, not just dates. If you need a date range as well, nest it: =OR(AND(ISNUMBER(A2),A2>=DATE(2026,1,1)),A2="N/A").

How to check whether a value is a real date

When a column looks right but sorts or calculates strangely, the values are probably text. A quick test in a helper column: =ISNUMBER(A2). TRUE means a genuine date serial; FALSE means text (or something else).

If you need to convert text to dates, be careful with DATEVALUE. Microsoft warns that it can return #VALUE! when the date text conflicts with your computer's regional date settings. Standardize the import or conversion procedure first, then add validation on top.

A short test set for any date column:

• A valid date in the middle of your range
• The first and last allowed dates (the boundaries)
• A blank cell
• Plain text, like "soon"
• An impossible-looking value, like 2/30/2026
• A value pasted in from another file

That list follows the testing workflow Microsoft describes, and the last item is where most rules fail in practice.

The locale trap: 03/04/2026

Excel reads date text through the system's regional settings. In a US workbook, 03/04/2026 is March 4. On a machine set to a day-first region, the same text can mean April 3. If you share a file with colleagues or clients abroad, a rule built on typed slash dates can accept or reject different values on different computers.

Three habits protect you:

• Build limits with DATE(year,month,day).
• Tell users to enter unambiguous dates such as 4-Mar-2026 where mixed settings are likely.
• Put the expected format in the Input Message.

Microsoft's documentation on DATEVALUE and regional settings explains why identical text behaves differently from one system to another.

Changing how the date looks (dd mmm yyyy and others)

After validation is set, you may want dates to display in a consistent style. To show dates like 04 Mar 2026:

• Select the range and press Ctrl+1.
• Choose Number > Custom.
• Enter dd mmm yyyy.

Per Microsoft's reference, dd is a two-digit day, mmm an abbreviated month name and yyyy a four-digit year. Remember this changes presentation only. It does not change the underlying serial value and does not fix text dates. Ctrl+Shift+# applies Excel's default date format quickly.

Automatic dates: TODAY versus a fixed stamp

People often ask how to have an automatic date in Excel. Two answers, for two needs:

• =TODAY() shows the current date and changes each time Excel recalculates or the workbook is reopened. Use it for "as of today" reports and for validation limits.
• Ctrl+; enters today's date as a fixed value that won't change later. Use it for entry dates, invoice dates, or anything that is a record.

Picking the wrong one is a classic reporting bug: an "invoice date" built with TODAY rewrites history every morning.

Why your validation rule is not working

If a rule seems ignored, work through this checklist. The causes come from Microsoft's troubleshooting advice.

• The value was pasted, filled or copied. Direct typing triggers the alert. Pasted, filled or copied values can bypass it.
• The rule wasn't applied to the whole range. Click a cell that fails and check that it has a rule.
• Formula errors or broken references. A limit pointing at a deleted cell or an error won't behave.
• Calculation mode is manual. Limits based on formulas may not have updated.
• The sheet is protected or shared in a way that limits the feature. Check the workbook's protection and sharing state.
• The Ignore blank setting lets empties through. Decide on purpose, then test.

There is also a built-in clean-up tool: from the Data Validation dropdown, you can choose Circle Invalid Data to see entries that break the rule after the fact. Pair it with conditional formatting for ongoing monitoring.

Perspective: validation is a gate, not an audit

We build LogicExcel around one idea: people learn spreadsheets by doing, not watching. Date validation is a good example. Most readers can follow the dialog in two minutes, but the skill that matters comes later, when a rule fails on a pasted value or a locale mismatch and you have to reason about why.

The pattern we see in real workbooks is a comfortable overconfidence. Someone sets a rule, sees the error alert fire once, and assumes the column is clean. But a rule only judges what is typed in after it exists. It says nothing about the 400 rows already there, the file imported from another system, or the dates that expired last week.

Our advice for anyone who owns a report: use validation to make the right entry easy, then add a second layer that checks the result. A helper column with ISNUMBER, a conditional format that highlights dates outside the window, and Circle Invalid Data before month-end close will catch what the gate misses. Managers building their own reports and analysts handing files to others benefit most, because they are the ones whose numbers get questioned.

And practice each piece on its own before combining them. A weekday rule, a boundary test and a text-versus-date check are three small skills. Together they are the difference between a workbook that looks controlled and one that is.

> — Thomas Houza

Practice these rules hands-on

Reading formulas is not the same as typing them. If you want to build the muscle memory, our free Excel exercises run in an Excel-like environment in your browser, with instant feedback and an explanation of each mistake. There is no account and no software to install. If you are starting from the basics, the exercises for beginners are the place to begin, and the practice set on IS, ODD, EVEN, YEAR and TODAY covers functions you just used in the custom rules above.

Date validation from code

If you automate Excel with Office Scripts or the Excel JavaScript API, the same logic applies. Microsoft's developer documentation shows date rules built from formula1, formula2 and an operator such as Between, EqualTo, GreaterThan or LessThan. Date rules accept ISO datetime strings or Excel formulas. A between rule takes two boundary operands, while a one-sided comparison takes one.

Thinking in those terms helps even if you never write code: every date rule is a value, an operator and one or two boundaries. When a rule gets complicated, restate it that way and you can usually see which part is wrong.

Quick recipes

Goal	Allow	Data / formula
Dates in 2026 only	Date	between =DATE(2026,1,1) and =DATE(2026,12,31)
Today or later	Date	greater than or equal to =TODAY()
Not in the future	Date	less than or equal to =TODAY()
Next 3 days	Date	between =TODAY() and =TODAY()+3
Weekdays only	Custom	=AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5)
Date or N/A	Custom	=OR(ISNUMBER(A2),A2="N/A")

For a broader reference to the functions behind these, keep our Excel functions cheat sheet handy.

FAQ

How do I do data validation in Excel?

Select the cells, go to Data > Data Validation, and choose a type under Allow, such as Date, List or Whole Number. Set the comparison and limits, add an input message and an error alert, then click OK. Test with a valid value, a boundary value and a bad one.

How do I check if a date is valid?

Use =ISNUMBER(A2) to confirm the cell holds a real date serial rather than text. For a range check, compare it with DATE limits, for example =AND(A2>=DATE(2026,1,1),A2<=DATE(2026,12,31)). Keep in mind that text dates depend on your regional settings.

Can people still enter invalid dates after I set a rule?

Yes. Typing triggers the alert, but pasted, filled or copied values can bypass it. Use Circle Invalid Data to find offenders, protect the sheet where it makes sense, and clean imported data before it enters the workbook.

Why does my date rule stop working as time passes?

A rule based on TODAY evaluates new and edited entries against the current date. Existing entries are not rechecked, so a date that was acceptable last month may now be out of range. Add conditional formatting or a helper column to flag them.

Sources

• Apply data validation to cells, Microsoft Support
• Format a date the way you want in Excel, Microsoft Support
• How to correct a #VALUE! error in the DATEVALUE function, Microsoft Support
• Add data validation to Excel ranges, Microsoft Learn

Recommended

• Excel Date Functions: TODAY, NOW, DATE, DATEDIF, DAYS
• IS, ODD, EVEN, YEAR and TODAY: Free Excel Practice Exercise