Excel date validation restricts what dates users can enter in a cell. In Microsoft Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, set Allow to Date, and specify the permitted date range; for more tailored rules, use a custom formula.
What date validation does
Data validation is a rule attached to one or more worksheet cells. For dates, it can accept dates within a range, before or after a boundary, or equal to a specified date. When someone enters a value that breaks the rule, Excel can stop the entry, show a warning, or display information while allowing the person to continue.
Excel stores dates as serial numbers and displays them according to the cell’s number format. A date such as January 15, 2026 is therefore a numeric value internally, not a special kind of text. Date validation checks the entered value against its rule; it does not, by itself, make a cell display like a date. Apply a date format separately if you want a consistent appearance.
This distinction matters when a value looks like a date but is actually text. Text such as a date copied from a website may not behave like a real Excel date in calculations or validation. Likewise, formatting a cell as Date changes how a suitable numeric value is displayed; formatting alone does not restrict what people can enter.
Set up a date rule in Excel
To limit a column to dates between two fixed endpoints, use the built-in Date option:
- Select the cells to control, such as A2:A100. Selecting the whole entry range before opening the dialog applies one rule to all selected cells.
- Choose Data > Data Validation. If Excel displays a menu, choose Data Validation again to open its settings.
- On the Settings tab, set Allow to Date.
- Choose a condition in the Data list, such as between, greater than, less than, or equal to.
- Enter the Start date and End date required by that condition. For a between rule, enter both limits.
- Optionally add guidance on the Input Message tab and configure the Error Alert tab.
- Choose OK, then test a valid date and a date outside the range.
For example, a rule set to Date, between, 1/1/2026 and 12/31/2026 accepts dates in that period and rejects dates outside it when the error alert is set to Stop. The way you type a date depends on your regional settings; use a format Excel recognizes in your installation. If an entry is ambiguous, such as 03/04/2026, Excel may interpret it as March 4 or April 3 depending on locale. A written month, such as 4-Mar-2026, can make an example clearer, but the accepted format still depends on regional settings.
In Excel for Mac, the Data tab also provides Data Validation, although dialog layout can vary by release. Excel for the web has data-validation controls as well, but some dialog details may differ from the Windows desktop version.
Choose date limits that fit the task
The built-in comparison choices cover many everyday needs. Select the rule that reflects what the entry should mean, not just a convenient boundary:
- Between: Use for a reporting period, booking window, or date of birth range.
- Greater than or equal to: Use when entries must be on or after a launch date.
- Less than or equal to: Use for a deadline or a latest permitted transaction date.
- Equal to: Use when every entry must match one particular date.
Before choosing boundaries, decide whether the endpoints count. A between rule includes its start and end dates. If a policy means strictly later than a date, choose greater than rather than greater than or equal to. Also consider whether the rule should apply to historical dates, future dates, weekends, or only dates within a particular calendar year. Those extra conditions may call for a custom formula.
For changing limits, you can enter formulas in the Start date and End date fields. With permitted dates in A2:A100 and the rule set to Date between, use =TODAY() as the start and =TODAY()+30 as the end to allow dates from today through 30 days from today. TODAY updates when Excel recalculates, so the window moves over time. Check that behavior suits the workflow: a date that was valid yesterday could fall outside a moving window later.
Use a custom formula for extra conditions
Choose Custom from the Allow list when a standard date comparison cannot express the complete rule. For example, a custom rule can check that a cell contains a numeric date within a particular year. With the selected range A2:A100 and A2 as its top-left cell, enter this formula in the Custom formula box:
=AND(ISNUMBER(A2),A2>=DATE(2026,1,1),A2<=DATE(2026,12,31))
This accepts numeric dates from January 1 through December 31, 2026. ISNUMBER helps reject text that merely looks like a date, while DATE creates the boundary values without relying on ambiguous date text. The formula uses A2 because that is the first cell in the selected range. Excel adjusts the relative reference for the other rows, checking A3 in the next row, then A4, and so on. If the selected range starts at A5, the formula should refer to A5 instead.
To allow weekdays only, add a weekday test. With A2:A100 selected and A2 at the top-left, use:
=AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5)
With the second WEEKDAY argument set to 2, Monday is 1 and Sunday is 7, so values 1 through 5 are weekdays. This rule checks the day of the week but does not impose a date range; combine it with boundary tests if both restrictions are needed. Excel formulas in validation can use relative references, so a mistaken fixed reference such as $A$2 may cause every cell in the range to be judged by the same cell.
A custom formula must evaluate to TRUE for an allowed entry. If you are testing a rule that must also reject blank entries, you could add a nonblank condition. With A2:A100 selected and A2 as the first cell, this formula requires a nonblank numeric date in 2026:
=AND(A2<>"",ISNUMBER(A2),A2>=DATE(2026,1,1),A2<=DATE(2026,12,31))
However, validation is not a complete required-field system: a blank cell can remain blank if nobody enters anything, and clearing existing content may not behave like entering an invalid value. Use a separate completeness check before processing a form or report.
Configure helpful messages and alerts
The Input Message tab can explain the expected entry when a user selects a cell. A useful message names the format or boundary, such as “Enter a date from January 1 through December 31, 2026.” It reduces guesswork, especially for shared templates. The message is guidance rather than the rule itself, so users may not see it in every workflow.
The Error Alert tab controls what happens after an invalid entry. A Stop alert prevents the entry from being accepted. Warning lets the user proceed after confirming, and Information displays a notice but does not enforce the same hard block as Stop. Use Stop when the range is mandatory, such as a period required by a data-import process. Warning can be appropriate when exceptions are legitimate but should be deliberate. A clear alert should say what range is allowed and how to correct the value.
Validation is useful for reducing input mistakes, but it is not a security boundary. It should not be treated as proof that a workbook contains only valid dates, particularly when data comes from imports, formulas, or other workbooks.
Common problems and how to check them
- A valid-looking date is rejected: The entry may be text, or Excel may have interpreted an ambiguous date differently. Re-enter it in a locally recognized format, or check whether the value is numeric and is displayed with a date format.
- An invalid date gets through: Pasting or filling cells can bypass the usual typed-entry prompt, and the pasted content may also replace the validation rule. Check the rule afterward and inspect the values rather than assuming every cell was validated.
- Only one row appears to be checked: A custom formula may refer to the wrong starting cell or use an absolute reference. The reference should normally match the top-left cell of the selected range and remain relative when each row should be checked independently.
- The cell shows a number instead of a date: The value may be a valid date serial with a General or numeric format. Apply a date format using Home > Number Format; changing the display format does not alter the validation rule.
- A rule is missing from some cells: Validation may not have been applied to the full range, or a paste operation may have changed it. Select representative cells and open Data > Data Validation to inspect their settings.
Excel’s Data Validation tools include Circle Invalid Data, which can help flag values in the selected area that do not meet its current rule. It is a review aid, not a substitute for checking that the rule itself matches the intended policy. A spreadsheet can also contain invalid values that were entered before validation was added, so applying a new rule does not automatically clean the existing data.
Date validation versus date formatting and other checks
Validation, formatting, and formulas solve different problems. Date validation controls data entry under a rule. Number formatting changes how values appear, for example from a serial number to 15-Jan-2026. A formula can identify or transform dates, and conditional formatting can highlight cells that meet a condition. None of those display features alone prevents an out-of-range value from being entered.
For a list of approved dates, a dropdown list can be easier to understand than a free-entry date range, especially when only a few dates are permitted. Use a list validation source for that case. For a continuous span such as any date in a calendar year, the built-in Date rule is usually simpler. If the dates are imported or pasted, add a review step such as a check column or a report of out-of-range entries instead of relying solely on the entry prompt.
Frequently Asked Questions
How do I allow only dates in Excel?
Select the target cells and choose Data > Data Validation. Set Allow to Date, choose a condition, and enter its limits. Set the Error Alert style to Stop if invalid typed entries should be blocked.
Can Excel date validation use today’s date?
Yes. For a date rule, enter a formula such as =TODAY() in a date boundary field. TODAY changes when Excel recalculates, so consider whether a moving limit is appropriate for records that must remain valid later.
Why does Excel accept a date outside my range?
Check that the alert style is Stop, that the rule covers the cell, and that a paste operation has not bypassed or replaced validation. Also inspect existing values: adding a rule does not guarantee that older entries conform.
Does date validation make a cell display as a date?
No. Set a date number format separately. Validation governs acceptable entries; formatting governs how a value is displayed.
Can I block weekends as well as dates outside a range?
Yes. Use Allow: Custom and a formula combining ISNUMBER, date boundaries, and WEEKDAY. Reference the top-left cell of the selected range so the check adjusts correctly for each row.
Related
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets: Get a List of Unique ValuesWith source values in A2:A, enter =UNIQUE(A2:A) in an empty cell to return each distinct value once. This…
- Insert a Row in a QuickBooks Journal Entry CorrectlyTo insert a row in a QuickBooks journal entry, open the entry and add a line: in QuickBooks…
- Named Range Sheets: How Excel Scope WorksIn Excel, a named range is a label for a cell or range, and its scope determines whether…
- Can You Split a Cell in Excel With Two Colors at Once?No—not as two independently filled halves of one cell. Excel gives a cell one overall fill color, but…
- Excel TEXT Percentage: How to Format Values as PercentagesIf A1 contains a decimal percentage such as 0.25, use =TEXT(A1,"0%") to display it as 25%. The TEXT…
- How to Double Space in Word: Quick Steps for Any DocumentTo double-space text in Microsoft Word, select the text—or press Ctrl+A on Windows or Command+A on Mac to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.