Let’s 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 that you specify.
Well, this can be done by using a custom formula based on the AND function and a simple way of defining dates in the date range option in data validation. Both methods will restrict a user to enter a date out of the range.
Steps to Create Date Validation with Date Range
- First, select the cell where you want to apply this data validation rule.
- Now, go to Data Tab ⇢ Data Validation ⇢ Data Validation.
- From here in the data validation dialog box, select “Date” from the “Allow” drop-down.
- After that, select between from the “Data” drop-down.
- Next, you need to enter two dates in the “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 that 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 at any time without opening the option. You can use the TODAY function to create a dynamic range of dates.
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 the data validation dialog box, select “Custom” from the “Allow” drop-down.
- Now, in the formula input bar enter the below formula and click OK.
This method is a bit limited and complex. But if you want to specify dates for one-time then you can use this method.