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.
=AND(A1>=DATE(2016,6,1),A1<=DATE(2016,6,30))
This method is a bit limited and complex. But if you want to specify dates for one-time then you can use this method.
Dear,
I am looking for a way to formulate that i select a number in one column and month in another column.
if the month is selected non 31 days it shld display error else it should accept.
For eg, if 31 November is selected, it shld desplay error.
thanks.
It is very useful
This is helpful trips.
Thanks.
Using date validation in my lot of work books. The issue I am facing in it are
1. data entry person can fill date outside of range by copying from other cell and pasting on validated cell .I really want a function or formula which can stop pasting any thing on specific cells (not on full sheet).
2.My sheets are always password protected but data entry person have the password so that he can enter data on the sheets (dates are also to be filled by him) he can change Validation range any time as per his requirement. Can we put any additional protection to avoid any change in validation range.
Sometimes when you want dates that may change over time you could place the date range in cells and then reference those cells in the rules. Like you quick tip I use the TODAY() option a lot with my date validations as often the end date is the current day.
SORRY IT IS NOT DATE IT IS DATA I NEED TO INSERT ADDITIONAL ITEMS ROWS IF I REQUIRE FOR MY ESTIMATE
I want to create a vba code for adding my rows with data and formula in same worksheet. code will give popup for which ro you want to insert. suppose my date is from rows a75:q148 i need to insert in row a76:q149
pls. send me the code on this
vipul