Date Validation in Excel: Formula Rules, Examples & Setup

Formulas & functions · Updated November 19, 2026

To validate dates in Excel with a formula, select the entry cells, open Data > Data Validation, choose Allow: Custom, and enter a rule such as =AND(ISNUMBER(A2),A2>=DATE(2024,1,1),A2<=DATE(2024,12,31)). This accepts real Excel dates from January 1 through December 31, 2024, in a range whose first selected cell is A2, and rejects other values.

Excel also has a built-in Date validation setting that is simpler for basic limits. A custom formula is useful when the rule combines conditions—for example, dates must fall within a period and be weekdays—or when the permitted period changes based on other cells. The key is to reference the first cell in the selected range correctly and to remember that Excel stores real dates as numbers.

What date validation does

Data validation controls what a person can enter directly into a cell. A rule can limit an entry to a date between two boundaries, display an instruction before entry, and show an error message when an entered value fails the rule. It helps standardize dates in forms, schedules, logs, and shared workbooks.

Validation is not the same as formatting. A date number format changes how a value appears, such as 1/5/2024 or 5-Jan-2024; it does not prove that the value is a valid date or keep it within a permitted period. Conversely, validation can accept a date while the cell displays it as a serial number if the cell is formatted as General. Use both validation and an appropriate date number format when appearance matters.

Excel represents dates internally as serial numbers, with a day corresponding to a number in its date system. A genuine date entered as January 5, 2024, is therefore a number Excel can compare with another date. Text that merely looks like a date may not be recognized or compared consistently. That distinction explains why a reliable custom rule often includes ISNUMBER.

Set up the formula rule

In Excel for Microsoft 365 on Windows, first select the cells where users will enter dates. If the selection begins at A2, use A2 in the formula, even when the selection covers many rows. Then go to Data > Data Validation. On the Settings tab, choose Custom in the Allow list and enter the formula in the Formula box.

  1. Select the target cells, such as A2:A100.
  2. Choose Data > Data Validation, then select Custom under Allow.
  3. Enter a formula that evaluates the entry cell to TRUE when it is permitted.
  4. Optionally set an input message and an error alert, then choose OK.

With A2:A100 selected and A2 as the top cell, the formula =AND(ISNUMBER(A2),A2>=DATE(2024,1,1),A2<=DATE(2024,12,31)) accepts dates from January 1 through December 31, 2024. The year, month, and day arguments in DATE avoid ambiguity from typing a date as text in a particular regional format. The AND function requires every condition to be true.

Do not make the cell reference absolute in this example. Excel adjusts the relative reference as the validation rule applies down the selected range: the next row is checked as A3, then A4, and so on. If the selected range starts at a different cell, replace A2 with its top-left cell. An incorrect starting reference can make Excel validate the wrong row.

Allow blanks or use date cells as boundaries

The example above requires a number, so it does not treat an empty cell as a valid date. If entries are optional, with the same A2:A100 layout, use =OR(A2="",AND(ISNUMBER(A2),A2>=DATE(2024,1,1),A2<=DATE(2024,12,31))). This makes a blank acceptable while continuing to require a real date within the range. Decide whether blanks should be allowed based on the purpose of the sheet; an optional date field and a required appointment date need different rules.

For a range that changes, store its start date in F1 and end date in F2, and format those cells as dates. With A2:A100 selected and the boundaries in F1 and F2, use =AND(ISNUMBER(A2),A2>=$F$1,A2<=$F$2). The dollar signs keep the boundary references fixed as the rule is applied to later rows, while A2 remains relative. For example, if F1 is January 1, 2025, and F2 is March 31, 2025, a genuine date in that period passes; a date before or after it fails.

Make sure F1 and F2 contain actual Excel dates rather than text that only looks like a date. You can enter them with DATE formulas or use Excel’s recognized date input format for your regional settings. Also check that the start date is not later than the end date; otherwise no ordinary date can satisfy the range.

Choose between built-in Date and Custom

For a straightforward date range, the built-in Date option is often easier to understand and maintain. In Data > Data Validation, choose Date under Allow, select an operator such as between, and provide the start and end values. This avoids writing a custom formula and is a good choice when the rule is simply “a date between these two dates.”

Choose Custom when the rule needs more logic, such as checking a date against cell references, allowing blanks conditionally, or combining a date range with a weekday test. Custom formulas are flexible, but they are less obvious to someone reviewing the workbook. Add a clear input message or a note in the sheet to explain the expected dates.

  • Use built-in Date validation for a basic minimum, maximum, or between rule.
  • Use Custom for multiple conditions or formula-driven boundaries.
  • Use a date number format separately to make accepted values easy to read.

Useful variations for real worksheets

Some date fields need a rule based on the current day rather than a fixed year. With A2:A100 selected, the formula =AND(ISNUMBER(A2),A2>=TODAY(),A2<=TODAY()+30) permits today through the next 30 days, including both endpoints. TODAY returns the current date, and adding 30 advances 30 days. Because the rule depends on the current date, its effective window moves as the date changes.

To require weekdays as well as dates in a fixed period, with A2:A100 selected, use =AND(ISNUMBER(A2),A2>=DATE(2024,1,1),A2<=DATE(2024,12,31),WEEKDAY(A2,2)<=5). WEEKDAY with the return type 2 numbers Monday as 1 through Sunday as 7, so values of 5 or less are Monday through Friday. The result excludes weekends but does not account for public holidays; holiday exclusions require an additional rule and a maintained holiday list.

For rules tied to a project or reporting period, cell-based boundaries are usually easier to update than editing a formula every year. For a rule such as “not before the start date and not after the end date,” put the two limits in clearly labeled cells and use absolute references to them. Avoid hard-coding a moving boundary unless it really is intended to stay fixed.

Error messages and existing data

On the Data Validation dialog’s Error Alert tab, keep Show error alert after invalid data is entered selected if users should be warned. A Stop alert blocks an invalid value entered normally; Warning and Information alerts let the user proceed after acknowledging the message. A specific message such as “Enter a weekday between the project start and end dates” is more useful than a generic warning.

Adding a rule does not clean up or certify values already in the selected cells. Review existing entries separately, especially when a sheet has been populated by imports or pasted data. Copying and pasting can also put values into validated cells without following the same ordinary typing prompt, so validation should not be treated as a complete data-audit system. For important records, use a separate review or check process as well.

Excel’s Data Validation dialog includes Circle Invalid Data, which can help identify entries that violate validation rules already applied to cells. It does not replace checking whether values are genuinely dates or whether the rule was applied to the right range. If a formula-based check is needed, a separate helper column can evaluate the source entries without changing them.

Common problems and how to fix them

  • A valid-looking date is rejected: The entry may be text. Re-enter it in a date format Excel recognizes for your locale, or convert the source data to real date values. A number format alone does not convert text into a date.
  • The wrong rows are checked: Confirm that the formula refers to the top-left cell of the selected range. For A2:A100, use A2—not A1 or A100—and keep that reference relative.
  • The rule changes unexpectedly by row: Relative references are meant to adjust for each row. Use dollar signs for fixed boundary cells, such as $F$1 and $F$2, but not for the entry cell when each row has its own date.
  • Dates display as numbers: Apply a date format to the entry cells. Validation determines what is allowed; formatting determines how the stored value appears.
  • A value appears despite the rule: Check whether it was already present, pasted into the range, or entered while the error alert was set to Warning or Information. Recheck the rule and inspect the values rather than assuming every visible entry was validated.

Regional date formats can also cause confusion. An entry such as 4/5/2024 can be interpreted differently depending on regional settings. DATE(2024,4,5) removes that ambiguity inside a formula, while instructions to users should match the workbook’s expected input format.

Frequently Asked Questions

Can Excel validate a date without a formula?
Yes. Select the cells, open Data > Data Validation, choose Date under Allow, and set an operator and date limits. Use Custom when the rule needs logic that the built-in Date choices do not cover.

Why does Excel accept a date outside my range?
Check that the rule was applied to the intended cells, that its references point to the correct row, and that the error alert is set to Stop if invalid typed entries must be blocked. Values already in the cells or pasted into them may need a separate review.

Does date formatting validate the date?
No. Formatting changes display, not the underlying value or permitted range. Use Data Validation to restrict entries, then apply a date format so accepted dates display clearly.

Can I allow a blank date cell?
Yes. Make the custom formula explicitly allow an empty entry, for example with an OR condition, or configure the rule to suit the worksheet’s blank-entry needs. Test both a blank and a date to confirm the behavior you want.

Will a TODAY-based rule update automatically?
TODAY returns the current date when Excel recalculates, so a moving date window can change over time. However, a date that was entered earlier is not necessarily removed or rechecked just because the allowed window later moves; review existing values when that distinction matters.

Choosing a reliable approach

For a fixed, simple date range, use the built-in Date rule. For a rule that combines conditions or reads limits from other cells, use a Custom formula with a real-date check, a relative reference to the first entry cell, and absolute references for fixed boundary cells. Explain the expected format, choose an appropriate error alert, and remember that validation helps guide data entry but does not replace reviewing imported, pasted, or previously stored values.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.