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.

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.

daterangesheet

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.

daterangesheet02

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 Examples

For more examples of data validation criteria, see Data Validation Criteria Examples on my Contextures web site.

______________________

2 comments to Restrict Date Entries with Data Validation

  • Tony Huby

    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

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>