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.
Set Up the Data Validation
After entering the start and end dates on the worksheet, follow these steps to set up the data validation:
- Select the cells where the data validation will be applied – cells B2:B6 in this example.
- On the Excel Ribbon, click the Data tab, and click Data Validation
- From the Allow drop down, select Date
- From the Data drop down, select Between
- Click in the Start Date box, and click cell E1, where the Start Date is entered.
- Press the F4 key, to change the cell reference to an absolute reference -- $E$1
- Click in the End Date box, and click cell E2, where the End Date is entered.
- Press the F4 key, to change the cell reference to an absolute reference -- $E$2
- Click OK, to close the 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.
For more examples of data validation criteria, see Data Validation Criteria Examples on my Contextures web site.
Sign up for weekly Excel news from Contextures