In Excel for Microsoft 365 on Windows, set a date rule by selecting the cells, choosing Data > Data Validation, and setting Allow to Date; then choose a condition such as between and enter the permitted dates. The important distinction is that data validation controls which dates users may enter, while the cell’s number format controls how an accepted date looks.
Date validation and date formatting are different
Excel does not have a special data-validation option that forces a particular visual date format. A rule can accept dates only within a range, before or after a date, or according to a custom formula. Separately, a number format determines whether an accepted value appears as 11/2/2026, 2-Nov-2026, or another display style.
Excel stores recognized dates as serial numbers, with time values represented as fractions of a day. The date format changes how that underlying value is displayed; it does not change the date itself. Likewise, applying a date format does not stop someone entering a date outside your intended range. For reliable data entry, set both the validation rule and the display format.
A cell that merely looks like a date may also contain text rather than a usable date value. Formatting text as a date does not necessarily convert it into a date, and validation may not behave as expected if the input is text. This is why it helps to test the rule with a valid date and an invalid one after setting it up.
Set a basic date rule in Excel for Windows
To limit entries to a date range, select the destination cells and open Data > Data Validation. On the Settings tab, choose Date under Allow. Select a condition in the Data list, such as between, and provide a start date and end date. The rule applies to the selected cells once you choose OK.
- Select the cells where users should enter dates, such as B2:B100.
- Choose Data > Data Validation.
- On Settings, set Allow to Date, then choose a condition such as between.
- Enter the lower and upper limits in the Start date and End date boxes.
- Optionally set an Input Message or an Error Alert, then choose OK.
The Date option includes conditions such as equal to, not equal to, greater than, less than, between, and not between. Use between when a date must fall inside a period, or greater than when only later dates are permitted. The boundaries of a between rule are included: a value equal to either limit is allowed.
You can enter a fixed date in the boundary boxes, or use a formula that returns a date. With B2:B100 selected and the validation rule set to between, the following boundary formulas define the 2026 calendar year: start date =DATE(2026,1,1) and end date =DATE(2026,12,31). The DATE function avoids ambiguous typed dates such as 1/2/2026, which can be interpreted differently under different regional settings.
Choose a date rule for the task
The right condition depends on what the date represents. A one-time event may need a fixed window, while a booking form may need to reject dates in the past. A report might require dates no later than its reporting cutoff. Decide whether the boundary is inclusive and whether the allowed window should move over time before choosing a rule.
- Fixed period: Use between for a defined project or reporting period.
- No past dates: Use greater than or equal to with a current-date formula as the start boundary.
- Deadline: Use less than or equal to with the deadline as the upper boundary.
- One exact date: Use equal to, for example for a single scheduled date.
- Exclude a blocked period: Use a custom formula or split the permitted periods into clearer rules or instructions.
For example, a booking sheet might accept dates from today through 30 days from today. With B2:B100 selected and the rule configured as Date and between, use =TODAY() for Start date and =TODAY()+30 for End date. These formulas recalculate as the current date changes, so the allowed window moves forward rather than staying fixed. If the workbook is intended to preserve a historical cutoff, use fixed dates instead.
Use a custom formula for more control
A custom validation formula is useful when the built-in Date conditions do not express the rule you need. For example, it can test a date range with a more complex condition, or combine date checks with another requirement. In Data Validation, change Allow to Custom and enter a formula that returns TRUE for an acceptable entry and FALSE otherwise.
For a custom formula applied to B2:B100, write the formula as if B2 were the first cell in the selection. This example accepts dates from January 1 through December 31, 2026: =AND(B2>=DATE(2026,1,1),B2<=DATE(2026,12,31)). Excel adjusts the relative B2 reference for each row, so the rule checks B3 in the next cell, B4 after that, and so on. If you apply the rule to a different range, update the reference to match its top-left cell.
Custom formulas are powerful but easier to misconfigure than the built-in Date option. A reference that points to the wrong row can make the rule seem inconsistent, and a formula that returns TRUE for invalid entries defeats the purpose. Keep the condition readable, test the first and last valid dates, and also test dates just outside each boundary.
Set how accepted dates appear
To control the display, select the validated cells and choose a date style from the Number Format list on the Home tab. For more control, open Format Cells with Ctrl+1 on Windows, select the Number tab, and choose Date or Custom. A custom format such as yyyy-mm-dd displays dates in an unambiguous year-month-day order. The format changes appearance, not the validation criteria or stored value.
It is often helpful to format the blank input cells before people start using the sheet. A format such as d-mmm-yyyy can make entries like 4-Nov-2026 easier to read than a short numeric date. Keep in mind that users can still type a value in another recognizable date style; the number format normally changes the displayed result after Excel recognizes the entry as a date.
Excel’s interpretation of typed dates can depend on regional settings. For example, 04/05/2026 may mean April 5 or May 4. A display format does not remove that ambiguity in the original entry. For shared workbooks, use a clear instruction, a less ambiguous display style, and formula-based boundaries when specifying fixed limits.
Improve the entry and error messages
Data Validation includes an Input Message tab and an Error Alert tab. An input message can appear when a user selects a cell and explain the required date window, such as “Enter a delivery date from today through the next 30 days.” An error alert can tell the user what to correct instead of presenting an unexplained rejection.
For the Error Alert, choose a style that matches how strict the sheet should be. Stop blocks a directly typed value that fails the rule. Warning lets a user continue after a warning, and Information presents a notice that can also be dismissed. If the rule is meant to enforce a requirement, use Stop and state the valid range in the message. The Ignore blank setting can allow an empty cell; if a date is mandatory, validation by itself may not be a complete solution for ensuring every required cell is eventually filled.
Good instructions reduce confusion. Say whether the end date is included, which date style is easiest to read, and whether a blank is permitted. If users enter dates through a form, imported file, or another system, test that route too; a worksheet rule is most useful when the people entering data understand what it expects.
Common problems and how to fix them
- The rule accepts an unexpected date: Check the condition, both boundaries, and whether the rule’s dates are formulas or fixed values. Test the exact boundary dates as well as one day outside the range.
- A valid-looking entry is rejected: The value may be text, or Excel may have interpreted a typed date using a different regional order. Re-enter it in a clear date style or use a DATE formula for fixed limits.
- The date displays as a number: The cell may have General or another number format. Apply a date format; the underlying value may still be a valid date.
- Changing the display format does not restrict entry: That is expected. Set a separate rule through Data > Data Validation.
- Some existing cells contain invalid dates: Adding a rule does not automatically repair or remove values already in the range. Review existing entries separately, then apply or reapply the rule.
- Pasting seems to avoid the rule: Data validation is not a complete security barrier. Copying or pasting data can introduce values that do not meet the rule, so audit or recheck data after imports and bulk edits.
Excel workbooks can use different date systems, and this matters most when dates are moved between workbooks or imported from other sources. A date that is valid in one workbook may not correspond to the same displayed day after a conversion problem. If dates look shifted after copying or importing, investigate the source data and workbook date settings rather than trying to fix the problem by changing the validation condition.
Excel for Mac and Excel for the web
The core idea is the same in Excel for Mac: select the cells, use the Data tab’s Data Validation command, and set a date criterion. Dialog wording and layout can vary slightly by platform and update, so look for the Allow or criteria setting and choose Date. On Mac, Command+1 opens Format Cells for changing the display format; the Windows shortcut is Ctrl+1.
Excel for the web also supports data validation for common entry restrictions, though the available controls and editing experience can differ from the Windows desktop application. If you cannot find a particular custom setting or need to inspect a complex rule, open the workbook in Excel for Microsoft 365 on Windows or Mac. In every version, keep the distinction clear: validation sets the acceptable input, while number formatting sets its appearance.
Frequently Asked Questions
Does Excel data validation change the date format?
No. It restricts acceptable entries but does not choose their display style. Set the number format separately from the Home tab or Format Cells.
How do I allow dates only within a range?
Select the input cells, open Data > Data Validation, choose Date under Allow, choose between, and enter the start and end dates. Use DATE formulas for fixed boundaries if you want to avoid regional date-entry ambiguity.
Can I prevent users from entering a date in the past?
Yes. Set a Date rule with a lower boundary based on today, or use a custom formula for a more specific condition. For a rolling range, use TODAY() as the start boundary and test the rule as the date changes.
Why does Excel show a number instead of a date?
Excel may be displaying the date serial value with a General or numeric format. Apply a date number format to the cell. If the value remains unchanged, check whether the entry is actually text rather than a recognized date.
Can users bypass date validation by pasting?
Yes, pasting and other bulk changes can introduce values that a person could not enter directly under the rule. Treat validation as an input aid, not a guarantee that all workbook data is valid, and review data after imports or pastes.
Related
- Excel Auto Group Rows: How to Group and Ungroup
- Excel Auto Group Rows by Cell Value: What You Can Do
- Excel Conditional Formatting on All Sheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Out of Office in Outlook 365: Set an Automatic ReplyTo set an out-of-office email in Outlook 365, open Automatic Replies for your account, turn on automatic replies,…
- Mail Merge in Word: How It Works and How to Use ItMail merge in Word combines a document template with a list of information—such as names and addresses—to create…
- Google Sheets DATEDIF: Calculate Days, Months, and YearsGoogle Sheets’ DATEDIF function calculates the time between two dates in whole days, months, or years. Enter a…
- Google Sheets COUNTIF by Color: Practical Ways to CountGoogle Sheets’ COUNTIF cannot count cells by fill color directly: it evaluates cell values against a criterion, not…
- Excel Find and Replace Everything After a CharacterTo remove everything after a character in Excel, press Ctrl+H, enter that character followed by an asterisk in…
- How to Move Freeze Panes in Excel to Different RowsTo move frozen rows or columns in Excel, first choose View > Freeze Panes > Unfreeze Panes. Then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.