Reject Invalid Dates in Excel

This example teaches you how to use data validation to reject invalid dates.

1. Select the range A2:A4.

2. On the Data tab, in the Data Tools group, click Data Validation.

Outside a Date Range

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: all dates between 5/20/2016 and today's date + 5 days are allowed. 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 enter an input message and error alert message, go to the Input Message and Error Alert tab.

Sundays and Saturdays

3. In the Allow list, click Custom.

4. In the Formula box, enter the formula shown below and click OK.

Explanation: the WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date. If a date's weekday is not equal to 1 (Sunday) AND not equal to 7 (Saturday), the date is allowed (<> means not equal to). In other words, Mondays, Tuesdays, Wednesdays, Thursdays and Fridays are allowed. Sundays and Saturdays are rejected. Because we selected the range A2:A4 before we clicked on Data Validation, Excel automatically copies the formula to the other cells.

5. To check this, select cell A3 and click Data Validation.

As you can see, this cell also contains the correct formula.

6. Enter the date 8/27/2016 (Saturday) into cell A2.

Result: Excel shows an error alert.

Note: to enter an input message and error alert message, go to the Input Message and Error Alert tab.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.