Restrict Date Entries with Data Validation

With Excel’s data validation, you can restrict the dates that can be entered on a worksheet. For example, you could specify start and end dates on the worksheet, and only dates within that range can be entered.

Start and End Dates on Excel Sheet

In the screen shot below, start and end dates are entered in column E, and dates in column B must be within that date range.

Start and End Dates on Excel Sheet
Start and End Dates on Excel Sheet

Set Up the Data Validation

After entering the start and end dates on the worksheet, follow these steps to set up the data validation:

  1. Select the cells where the data validation will be applied – cells B2:B6 in this example.
  2. On the Excel Ribbon, click the Data tab, and click Data Validation
  3. From the Allow drop down, select Date
  4. From the Data drop down, select Between
  5. Click in the Start Date box, and click cell E1, where the Start Date is entered.
  6. Press the F4 key, to change the cell reference to an absolute reference — $E$1
  7. Click in the End Date box, and click cell E2, where the End Date is entered.
  8. Press the F4 key, to change the cell reference to an absolute reference — $E$2
  9. Click OK, to close the Data Validation window.
Data Validation window
Data Validation window

Watch the Video

To see the steps for applying this data validation, please watch this short video tutorial.

It also shows you how to set up a formula that will validate dates from today, to 6 days from now.

More Date Validation

Here’s another example of data validation for dates in Excel. This video shows 3 ways to validate dates.

  • Specify a starting date and an ending date. (Date option)
  • Show a drop down list of valid dates (List option)
  • Create a rule in a custom formula (Custom option)

Written instructions, and the sample file, are on the Data Validation for Dates page, on my Contextures site.

______________________

0 thoughts on “Restrict Date Entries with Data Validation”

  1. I had to do this some time ago. We have a worksheet that user have to enter a date from the current month when a piece of correspondence comes in, then another date when the reply is issued. Another cell calculate the days between received and issued. The reply issued date has had to be limited to a date >= the date received. We got some great figures in the calculted cell without this.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.