How to Create a Data Validation with Date Range

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.

Quick Tip: You can use 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 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))

This method is bit limited and complex. But, if you want to specify dates for one-time then you can use this method.

Content Protection by DMCA.com
2018-11-16T06:13:26+00:00

3 Comments

  1. Andrew Evans 8 Sep, 17 at 1:06 pm - Reply

    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.

    • VIPUL SHAH 9 Sep, 17 at 7:47 am - Reply

      SORRY IT IS NOT DATE IT IS DATA I NEED TO INSERT ADDITIONAL ITEMS ROWS IF I REQUIRE FOR MY ESTIMATE

  2. VIPUL SHAH 8 Sep, 17 at 10:15 am - Reply

    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

Leave A Comment