Reject Invalid Dates

 

Outside a Date Range | Sundays and Saturdays

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

1. Select the range A2:A4.

Select the Range in Excel

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

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.

Validation Criteria

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.

Dates Outside the Date Range are Rejected

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.

Data Validation Formula

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.

Formula Check

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.

Sundays and Saturdays are Rejected

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