Reject Invalid Dates in Excel
This example shows how to use data validation to prevent entering invalid dates.
1. Select the range A2:A4.
2. Go to the Data tab, and in the Data Tools group, click Data Validation.
💎 Outside a Date Range
Excel data validation for date entry:
3. In the Allow list, click Date.
4. In the Data list, click between.
5. Enter the Start date and End date shown below and click OK.
Explanation: Only the dates from May 20, 2016, up to five days after today are allowed for validation. All dates outside this date range are rejected.
6. Enter the date 5/19/2016 into cell A2.
Result: Excel shows an error alert.
Note: To add an input message or an error alert, go to the Input Message and Error Alert tabs.
💎 Sundays and Saturdays
3. In the Allow list, click Custom.
4. Enter the formula provided below in the Formula box, then click OK to confirm.
Explanation: The WEEKDAY function gives a number from 1 to 7, where 1 stands for Sunday and 7 stands for Saturday, based on the date you enter. A date is considered valid if its weekday is neither Sunday (1) nor Saturday (7). (<> means not equal to).
In other words, only the days from Monday to Friday are acceptable. Sundays and Saturdays are rejected. Since we selected the range A2:A4 before clicking Data Validation, Excel applied the same formula to all the selected cells automatically.
5. To verify this, select cell A3 and click on Data Validation.
You can see that this cell contains the correct formula too.
6. Click on cell A2 and write 8/27/2016 (Saturday).
Result: Excel shows an error alert.
Note: Go to the Input Message tab to write a message, and the Error Alert tab to set an alert.
2/9 Completed! Learn much more about data validation ➝
Next Chapter: Keyboard Shortcuts