The quickest way to add date validation in Excel is to use Data Validation with a formula. For example, to allow only dates in 2025, select the cells, go to Data > Data Validation, choose Custom under Allow, and enter =AND(A2>=DATE(2025,1,1),A2<=DATE(2025,12,31)) (adjusting A2 to the top-left cell of your selection). This restricts entry to valid dates within a range you define.
Why Use a Formula for Date Validation?
Excel’s built-in date validation options (like Date between two fixed dates) are simple, but they have limits. They can’t reference other cells, handle complex conditions, or check for weekdays or holidays. A custom formula gives you full control. For instance, you can ensure a date is not in the past, falls on a weekday, or is within a dynamic range based on other cells. This is especially useful in project trackers, booking forms, or financial models where data integrity matters.
How to Set Up a Custom Date Validation Formula
- Select the cell or range where you want the validation (e.g.,
B2:B100). - Go to Data > Data Validation (on Mac: Data > Data Validation).
- In the Allow drop-down, choose Custom.
- In the Formula box, enter your formula. The formula must reference the active cell (the first cell in your selection). For example, if you selected B2:B100, the active cell is B2, so your formula should reference B2.
- Click OK.
For Excel for the web, the steps are the same. On Mac, the menu path is identical. Note that the formula must return TRUE for valid entries and FALSE for invalid ones.
Common Date Validation Formulas and Examples
Here are practical formulas you can copy. Always adjust the cell reference to match your active cell.
- Allow only future dates (including today):
=B2>=TODAY() - Allow only weekdays (Mon-Fri):
=WEEKDAY(B2,2)<6(where 2 means Monday=1) - Allow dates within the current month:
=AND(B2>=EOMONTH(TODAY(),-1)+1, B2<=EOMONTH(TODAY(),0)) - Allow dates between two cells (e.g., start in D2, end in E2):
=AND(B2>=$D$2, B2<=$E$2) - Allow only weekends (Sat/Sun):
=OR(WEEKDAY(B2,2)=6, WEEKDAY(B2,2)=7)
For example, with start date in D2 (2025-01-01) and end date in E2 (2025-12-31), entering 2025-06-15 in B2 passes, but 2026-01-01 fails.
How to Choose the Right Formula
Think about what you’re trying to prevent. If you just need a date range, use the built-in Date validation with a start and end date. If you need dynamic logic, use a custom formula. Key questions:
- Is the range fixed or based on other cells? Use cell references.
- Do you need to exclude weekends or holidays? Use
WEEKDAYor a list of holidays withCOUNTIF. - Do you need to ensure the date is entered as a real date, not text? Use
=ISNUMBER(B2). - Do you want to allow blank cells? Add
OR(B2="", ...)to your formula.
Remember, the formula is evaluated for each cell, and the active cell reference is key. If you copy the validation to other cells, relative references adjust automatically.
Common Problems and How to Fix Them
- Validation doesn’t work on existing data: Data validation only applies to new entries. To check existing data, use Data > Data Validation > Circle Invalid Data (not available in Excel for the web).
- Formula returns an error: Ensure the formula is correct and references the active cell. For example, if your selection starts at B2, your formula must reference B2, not A2.
- Copying validation to other cells: Use Copy and Paste Special > Validation to copy the rule without overwriting other formatting.
- Dates entered as text: If a user types “2025-01-01” as text, validation may fail. Use
=ISNUMBER(B2)to ensure real dates. - Validation is ignored when pasting: Pasting data can bypass validation. Use Data > Data Validation > Error Alert to warn, but note that pasting can still override.
Using Formulas for Dynamic Date Ranges
Formulas shine when the allowed range changes over time. For example, to allow dates within the next 30 days: =AND(B2>=TODAY(), B2<=TODAY()+30). To allow dates in the current quarter: =AND(B2>=DATE(YEAR(TODAY()),CHOOSE(QUARTER(TODAY()),1,4,7,10),1), B2<=EOMONTH(DATE(YEAR(TODAY()),CHOOSE(QUARTER(TODAY()),1,4,7,10)+2,1),0)). These formulas recalculate when the workbook opens, so the validation stays current.
Frequently Asked Questions
Can I use a formula to validate a date is not a holiday?
Yes. Create a list of holiday dates in a range (e.g., H2:H10). Then use =AND(ISNUMBER(B2), COUNTIF($H$2:$H$10,B2)=0). This returns TRUE only if the date is a real date and not in the holiday list.
Why does my validation formula work for some cells but not others?
This usually happens when the formula uses relative references. If you set the validation for B2:B10 with formula =B2>=TODAY(), it works because B2 is the active cell. But if you later apply the same rule to a different range without adjusting the reference, it may fail. Always ensure the formula references the top-left cell of the selected range.
Can I use a named range in the formula?
Yes. For example, if you define a named range StartDate and EndDate, you can use =AND(B2>=StartDate, B2<=EndDate). This makes the formula easier to read and maintain.
Does data validation work with Excel tables?
Yes. When you apply validation to a column in an Excel table, new rows added to the table automatically inherit the validation rule. The formula should reference the first data cell in that column, like [@Date] in structured references, but the standard approach works too.
How do I remove date validation?
Select the cells, go to Data > Data Validation, click Clear All, and then OK. This removes all validation rules from the selection.
Safety and Accuracy Notes
Always test your validation formula with sample data to ensure it behaves as expected. Remember that data validation is not a security feature—it can be bypassed by pasting or by copying cells. For critical data, consider using conditional formatting to highlight invalid entries as a backup. Also, be aware that dates in Excel are stored as serial numbers, so formulas like ISNUMBER can help distinguish real dates from text. If you’re using dates for financial calculations, double-check that your formulas account for leap years and time zones if applicable.
Related
- Excel Calendar Template for Events: How to Build One
- Excel Business Template for Invoice: How to Use
- Excel Business Template for Profit and Loss
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Link Cells in Excel: Formulas, Sheets, and WorkbooksYes. In Excel, you can link cells with a formula that displays another cell’s value, whether the source…
- Excel QR Code Formula: Create QR Codes from Cell DataExcel has no built-in QR-code function, but in Microsoft 365 for Windows you can display a QR code…
- How to Sort in Alphabetical Order in ExcelTo sort a list alphabetically in Excel for Microsoft 365 on Windows, click a cell in the text…
- C String Manipulation Functions: A Practical GuideC string manipulation in standard C is provided mainly by functions declared in <string.h>, including strlen, strcpy, strcat,…
- Compound Interest Calculator in Excel: Formulas and TipsYes. In Microsoft Excel for Microsoft 365 on Windows, enter the starting balance, annual interest rate, number of…
- Create a Sequence of Dates in Excel: Formulas and FillTo create a sequence of dates in Excel, enter a starting date and use =SEQUENCE(10,1,DATE(2026,1,1),1) to spill ten…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.