How to discard invalid dates in Excel

• Out of date range
• Sundays and Saturdays

This example explains how to use data validation to discard invalid dates.

1. Highlight a range A2: A4.
2. On the Advanced tab Data (data) click Data Validation (Data validation).

Out of date range

3. From drop down list Allow (data type) select item Date (Date).
4. From drop down list Data (Value) select item Between (Between).
5. Enter the start and end date as shown in the picture below and click OK.Explanation: Dates between May 20, 2013 and today + 5 days are allowed. Dates outside this range are invalid.
6. Type in cell A2 the date is May 19, 2013.

Result: Excel issues an error message.

Note: To specify an input hint or text to alert you to an error, click the tab Input Message (Message for input) or Error Alert (Error message).

Sundays and Saturdays

3. From drop down list Allow (data type) select item Custom (Another).
4. In the Formula (Formula) Enter the formula shown below and click OK.

   =AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7)

   =И(ДЕНЬНЕД(A2)<>1;ДЕНЬНЕД(A2)<>7)

   

   Explanation: Function WEEKDAY (WEEKDAY) returns a number between 1 (Sunday) and 7 (Saturday) representing the day of the week. If the day of the week is neither 1 (Sunday) nor 7 (Saturday), the date is allowed (<> means not equal). In other words, Mondays, Tuesdays, Wednesdays, Thursdays and Fridays are valid. Sundays and Saturdays – no. Since, before opening the data validation window, we selected the range A2: A4, Excel automatically inserted the formula into all cells in that range.

5. To check this, select a cell A3 And click the Data Validation (Data validation).As you can see in the figure, this cell also contains a formula.
6. Enter the date August 24, 2013 (Saturday) in the cell A2.

Result: Excel issues an error message.

Note: Click the tab Input Message (Message for input) or Error Alert (Error Message) to write an input hint or text to alert you to an error.