Let say you need to create a data validation for dates in which you want to use a specific date range.
In simple words, you want when a user enters a date in a cell that should be within a range of dates which you specify.
Well, this can be done by using a custom formula based on the AND function and a simple way by defining dates in the date range option in data validation.
These both methods will restrict a user to enter a date out of the range.So, let learn this thing.
Steps to Create Date Validation with Date Range
- First of all, select the cell where you want to apply this data validation rule.
- Now, go to Data Tab ⇢ Data Validation ⇢ Data Validation.
- From here in data validation dialog box, select “Date” from “Allow” drop down.
- After that, select between from the data drop down.
- Next, you need to enter two dates in “Start Date” and “End Date” input boxes.
- In the end, click OK.
Now, in all those cells which you have selected user can only enter a date which is within the range of those dates which you have specified.
Even, instead of entering dates directly into the data validation you can refer to the cells where you have dates.This way you can change dates any time without opening the option.
Using AND Function to Create a Date Range in Data Validation
The same thing can also be done by using a custom formula based on AND function.
- First of all, select cell A1.
- From data validation dialog box, select “Custom” from “Allow” drop down.
- Now, in the formula input bar enter below formula and click OK.
=AND(A1>=DATE(2016,6,1),A1<=DATE(2016,6,30))
