Excel Data Validation for Dates: Rules and Examples

In Excel for Microsoft 365 on Windows, date validation restricts what dates users can enter in selected cells. Select the cells, choose Data > Data Validation, set Allow to Date, and define a condition such as a permitted start and end date; you can also use a rule based on TODAY() to keep the allowed period current.

What date validation does

Data validation is a cell-entry rule, not a date format. It can accept dates only within a specified range, before or after a cutoff, or equal to a particular date. When someone enters a value that breaks the rule, Excel can show a message and, if the error alert is set to Stop, prevent the entry. This is useful for forms, schedules, order logs, and other workbooks where inconsistent dates make filtering or calculations unreliable.

Validation does not change how a date is displayed. A cell may have a rule requiring a date between two limits and still show an accepted date as 11/2/2026, 2-Nov-2026, or another format. The display is controlled separately through number formatting. Conversely, formatting a cell to look like a date does not guarantee its value is a valid date; a text string can resemble one without behaving like an Excel date in calculations.

Excel normally stores dates as serial numbers and displays them using a date format. A date rule checks the underlying value and condition, rather than whether the cell looks a certain way. That distinction explains why a correctly formatted-looking entry can sometimes fail validation, and why a date can display differently on another person’s computer.

How to set a date rule

On Windows in Microsoft 365, select the target cell or range and open Data > Data Validation. In the Settings tab, choose Date from the Allow list. Choose a comparison in the Data list, then enter the needed limit or limits in the boxes that appear. For a range, select between and enter both a start date and an end date. Select OK to apply the rule.

Select the cells that should accept dates, such as B2:B100.
Open Data > Data Validation and stay on the Settings tab.
Choose Date under Allow, then choose the comparison, such as between or less than or equal to.
Enter the date limits. You can type a date or use a formula that returns a date, such as =TODAY().
Review the Input Message and Error Alert tabs if you want instructions or a warning, then select OK.

The date you type into a limit box must be understood as a date by Excel. Depending on your regional settings, 4/5/2026 can mean April 5 or May 4. To reduce ambiguity, use a clear date format in the sheet and consider formulas such as =DATE(2026,4,5) for a fixed limit. The DATE function builds a date from year, month, and day numbers, avoiding uncertainty about the order of typed components.

Excel for Mac has the Data Validation command on the Data tab, and the dialog uses the same basic Allow, comparison, and limit settings. Ribbon arrangement and dialog appearance can vary by Excel build. In Excel for the web, validation can be viewed and managed, but the available options and interface can differ from the Windows desktop dialog; use desktop Excel if a particular setting is not available in your browser.

Choosing the right date condition

The correct operator depends on the task. For an event booking window, between ensures each entry is no earlier than opening day and no later than the closing day. For a historical log, less than or equal to a cutoff can keep future dates out. For a forward-looking appointment list, greater than or equal to a permitted start date may be enough. Excel also offers equality and inequality conditions when only a specific date or exclusion is needed.

Between: accept dates within a start-to-end window, such as a reporting quarter.
Less than or equal to: allow dates on or before a deadline or cutoff.
Greater than or equal to: require a date on or after a launch date.
Equal to: require one exact date, for example a single scheduled inventory day.
Not between: exclude a period, such as a planned system shutdown.

Think through whether the boundary dates themselves should be allowed. Between includes its start and end values, so a rule from April 1 through June 30 accepts both April 1 and June 30. If the rule should exclude a boundary, use an appropriate greater than or less than condition instead. Writing the policy in an adjacent note or input message can prevent users from guessing what the limits mean.

Using TODAY for rolling date windows

A fixed limit becomes outdated as time passes. For a rule that should allow dates from today through 30 days from today, set Allow to Date, choose between, and enter =TODAY() as the start and =TODAY()+30 as the end. TODAY() returns the current date according to Excel’s calculation environment; adding 30 advances the serial date by 30 days. This creates a rolling window rather than a range tied to the day the rule was first created.

For a past-date-only field, choose less than or equal to and use =TODAY() as the limit. For a future-only field, choose greater than and use =TODAY() if today itself should not be accepted; choose greater than or equal to if today is allowed. These small operator choices matter in booking and deadline forms.

If the window should depend on a date entered elsewhere, use a cell reference instead. For example, if B1 contains a real Excel date and entries must fall within the following 30 days, use $B$1 as the start and =$B$1+30 as the end. The dollar signs keep the reference anchored when the rule is applied across a range. Make sure B1 itself contains a date value, not text that merely looks like one.

Dynamic formulas are convenient, but they can make a historical form behave differently later: the allowed window moves when TODAY() changes. For records that must retain the original submission rules, consider documenting the applicable cutoff or using fixed dates. Validation is not an audit trail and does not record which limits were in effect when someone entered a value.

Custom rules for weekdays and other cases

The built-in Date option covers ordinary comparisons, but a custom formula can handle additional logic. For example, to allow only weekday dates in cells A2:A100, select that range, open Data > Data Validation, choose Custom under Allow, and enter =AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5). A2 must be the top-left cell of the selected range. WEEKDAY with a return type of 2 numbers Monday as 1 through Sunday as 7, so values up to 5 are weekdays. ISNUMBER helps reject text that resembles a date.

Custom rules are useful when a field must meet more than one condition, but they require careful reference design. The reference to A2 is relative, so Excel evaluates A3 for the next row, then A4, and so on. If the formula instead used $A$2, every row would be judged against the same cell, which is usually not what a row-by-row rule intends. Test the first row, a middle row, and the final row before distributing a workbook.

Custom validation can also combine a date boundary with another requirement, such as excluding a holiday list, but formulas can become difficult to maintain. For a small, stable form, a clear built-in Date condition is easier for another person to inspect. If a complex rule is needed, document it near the input area and verify it after adding or moving rows.

Input messages, error alerts, and existing entries

In the Input Message tab of the Data Validation dialog, you can show a short instruction when a user selects a cell. A practical message might say Enter a date from today through 30 days ahead. This makes the rule visible before a user types, which is especially helpful when the date field is part of a shared template.

The Error Alert tab controls the response to invalid entries. Stop prevents a user from accepting a value that violates the rule through ordinary direct entry. Warning allows the user to proceed after confirming, while Information displays a message without enforcing the same strict block. Choose the alert style to match the purpose: a required business constraint calls for Stop, while a soft guideline may warrant a warning.

Adding a rule does not automatically repair or remove values already in the selected cells. It also does not guarantee that every value added through copying, importing, or other editing routes has been checked in the same way as ordinary typing. After applying a rule to a populated range, inspect the existing values. In desktop Excel, the Data Validation menu includes Circle Invalid Data, which can highlight entries that fail the current rule. Resolve those values before relying on the sheet’s results.

Common date-validation problems

A frequent issue is a date that looks right but is stored as text. This can happen after pasting from a website, importing a CSV file, or using a date format Excel does not recognize under the current regional settings. If validation rejects a plausible entry, try entering the date directly in the cell using an unambiguous form recognized by your system, or convert the source data to real date values before applying the rule.

Another common issue is selecting the wrong range or creating a formula relative to the wrong starting cell. If a custom rule is applied to A2:A100, the formula should normally refer to A2, not A1 or a fixed $A$2. Likewise, if the dialog’s Ignore blank option is selected, empty cells may remain permitted even though nonblank entries are restricted. Decide whether blanks should be allowed; validation of dates does not by itself make a cell mandatory.

Users may also confuse display formats with allowed values. Applying a short-date number format will not stop a user from entering an invalid date, and setting a date validation rule will not force a particular display style. Set both separately when the workbook needs consistent appearance and reliable date values. When a rule appears not to work, check the selected cells, the comparison limits, the Error Alert style, and whether the entered value is actually a date serial.

Designing a dependable date-entry sheet

A good rule is only one part of a usable spreadsheet. Label the date column clearly, use an input message for less obvious limits, and choose a consistent date format. For example, a leave-request form can say Requested start date and show a message explaining that the date must fall within the current request period. A separate end-date column may need its own rule, and you may need a formula-based check to ensure the end date does not come before the start date.

Apply validation to the full intended entry area, not just the first row. If the workbook will grow, include a reasonable number of blank rows or use an Excel Table so newly added rows can inherit table structure and formatting. Confirm that the validation rule is present in the rows users will actually fill in. When copying cells, be deliberate: copying can also copy validation settings, which may replace a destination cell’s existing rule.

Finally, treat validation as a helpful guardrail rather than a substitute for reviewing data. It guides normal entry, but it cannot establish that a date is factually correct. A date can be in range and still be the wrong appointment date. For high-impact records, pair validation with clear instructions, review procedures, and checks that compare related fields.

Frequently Asked Questions

How do I restrict Excel cells to dates only?
Select the cells, open Data > Data Validation, choose Date under Allow, and set a suitable condition. Use a Stop error alert if ordinary direct entry should be blocked when it does not meet the rule.

Can Excel allow dates only between two dates?
Yes. Choose Date, select between, and enter the earliest and latest permitted dates. Both endpoints are included. You can use fixed dates or formulas such as =TODAY() and =TODAY()+30 for a moving window.

Why is Excel rejecting a date that looks valid?
The value may be text rather than a date serial, or Excel may be interpreting the day and month in a different regional order. Check the cell’s actual value, try entering a recognized date directly, and verify the rule’s limits and comparison operator.

Does date validation make a cell required?
No. Date validation restricts nonblank values according to its rule, but it does not necessarily require an entry. To make a field mandatory, explain that requirement in the sheet and use an appropriate completion check; also review the Ignore blank setting.

Does validation check dates already in the cells?
Applying a rule does not automatically correct existing entries. Use Data Validation’s Circle Invalid Data command in desktop Excel to identify values that fail the current rule, then review and correct them.

Can I prevent weekends from being entered?
Yes. Use a Custom validation rule such as =AND(ISNUMBER(A2),WEEKDAY(A2,2)<=5) for a selected range beginning at A2. Ensure the formula refers to the range’s top-left cell so it adjusts correctly for each row.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.