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.
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.
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.
______________________
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.
@Tony, thanks for sharing your data validation example. People can get very creative if you don’t put some limits on the cells!