How to Add Date Validation in Excel (Restrict to Valid Date Ranges)

Basic Date Range Validation

To restrict a cell to dates within a specific range:

1. Select the cell(s) to validate
2. Data tab → Data Validation
3. Under Allow, choose “Date”
4. Under Data, choose “between”
5. Set Start and End dates

Example: Allow only dates in 2025: Start = 1/1/2025, End = 12/31/2025.

Dynamic Date Ranges

Instead of hardcoded dates, use formulas for dynamic validation:

Only future dates: Data = “greater than”, Value = =TODAY()

Only past dates: Data = “less than”, Value = =TODAY()

Within last 30 days: Data = “between”, Start = =TODAY()-30, End = =TODAY()

Weekdays Only

To allow only weekdays (business days), you need a custom formula:

1. Allow = “Custom”
2. Formula: =WEEKDAY(A1,2)<6

WEEKDAY with argument 2 returns 1-7 for Monday-Sunday. Less than 6 means Monday through Friday.

Excluding Holidays

Create a list of holiday dates on another sheet, then use:

=AND(WEEKDAY(A1,2)<6, COUNTIF(Holidays!$A:$A,A1)=0)

This allows only dates that are weekdays AND not in the holiday list.

Input Messages and Error Alerts

In the Data Validation dialog:

Input Message tab: Shows a tooltip when the cell is selected. Use this to explain what's expected: “Enter a date in 2025 (weekdays only).”

Error Alert tab: What happens when validation fails. “Stop” rejects the entry entirely. “Warning” or “Information” allows override.

Limitations of Data Validation

Data validation has weaknesses:

• Copy-paste can bypass validation entirely
• Users can delete the validation rules
• No visual calendar — users still type dates manually
• Doesn't prevent text-formatted dates

Official Resources

• Apply data validation — validation setup guide
• Create drop-down lists — validation with lists