Data Validation → Date in Excel is useful when you want to allow users to enter only valid dates within a specific range.
Example: Allow dates only from 1-Jan-2026 to 31-Dec-2026
Suppose you have this worksheet:
| Employee | Joining Date |
|---|---|
| Rahul | 15-Jan-2026 |
| Priya | 20-Mar-2026 |
| Amit | 10-Jul-2026 |
You want the Joining Date column to accept only dates in 2026.
Step 1 — Select the cells
Select the cells where dates will be entered, for example:
B2:B20
Step 2 — Open Data Validation
Go to:
Data → Data Validation → Data Validation
Microsoft's Excel guidance confirms that Data Validation can restrict a cell to dates and lets you choose a restriction such as "between."
Step 3 — Choose Date
In the Settings tab:
-
Allow:
Date -
Data:
between -
Start date:
01/01/2026 -
End date:
31/12/2026
The dialog should look approximately like this:
Step 4 — Add an error message
Click the Error Alert tab.
For example:
- Style: Stop
- Title: Invalid Date
-
Error message:
Please enter a date between 01-Jan-2026 and 31-Dec-2026.
Click OK.
Now, if someone enters:
-
✅
15/05/2026→ accepted -
✅
31/12/2026→ accepted -
❌
15/05/2025→ rejected -
❌
15/01/2027→ rejected
Another useful example: Today to 7 days from today
You can make the validation dynamic.
Set:
Allow: Date
Data: between
Start date: =TODAY()
End date: =TODAY()+7
This means the cell accepts dates from today through the next 7 days. Microsoft also documents using formulas such as TODAY() in date-validation limits.
Important tip
Excel stores dates internally as sequential numbers, so a value that looks like a date should actually be recognized by Excel as a date.
