Go to the Input Message tab, and add a message to help people input the correct date
Go to the Error Alert tab, and add a custom error message to help people who have input an incorrect date . Or, turn off the warning message, to allow invalid entries.
Then, click the OK button, to close the data validation dialog box, and to apply the new data validation rule
Type Dates on a Sheet
To make it easier to see and adjust the data validation rules, you can type the start and end dates on a worksheet. Then, name those cells, and refer to those names in the rules.
Set up the Date Cells
On any sheet in the workbook, type "StartDate" in one cell, and type "EndDate" in the cell below.
In the cells to the right of those labels, type a starting date and an ending date for the valid date range.
Select all four cells, and on the Excel Ribbon, click the Formulas tab
Click Create from Selection, and in the dialog box, check Left Column, then click OK
The cells are named, based on the labels in the first column -- StartDate and EndDate
Set up the Data Validation
Select cell C4, and on the Excel Ribbon, click the Data tab
Click Data Validation (click the upper section of the command)
On the Settings tab of the data validation dialog box, from the
Allow drop down, click Date
In the Data drop down, leave the default setting of Between, because
we want to limit the entries to dates between specific start and
Click in the the Start Date box, and press the F3 key on the keyboard, to open the Paste Names dialog box, then double-click on StartDate in the list, to select it.
Click in the the End Date box, press F3, then double-click on EndDate, to select it.
Click OK, to close the data validation dialog box.
Test the Data Validation
To test the data validation, enter a date that is not in the year,
such as January 1, 2016, and press Enter.
An error message appears, and blocks the invalid entry. Note:
You can customize the message that appears for invalid entries,
by using the data
validation Error Alert tab.
Click Retry or Cancel, and enter a date that is in the year 2017.
The entry will be accepted, and no message appears.
Create Dynamic Drop Down List of Dates
If only a short range of dates is valid, such as yesterday, today
and tomorrow, you can create a drop down list of those dates. By using
formulas, the drop down list will be dynamic, and will show the correct
dates whenever the workbook is opened.