Excel Data validation allow weekday only

Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula if you need more control or flexibility.

To allow a user to enter only dates that are weekdays (i.e. Monday, Tuesday, Wednesday, etc.) you can use data validation with a custom formula based on the WEEKDAY function.

In the example shown, the data validation applied to C5:C7 is:

=WEEKDAY(C5,2)<6

How this formula works

Data validation rules are triggered when a user adds or changes a cell value.

This custom validation formula uses the WEEKDAY function to get a numeric value, 1-7, corresponding to to a week beginning Monday (1) and ending Sunday (7). To get a number for a Monday-based week, the return_type argument for WEEKDAY is provided as 2.

The WEEKDAY result is then compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and validation succeeds. If the weekday number is not less than 6, validation fails because the date is a Saturday or Sunday.

Date is weekend

To allow only dates that occur on a weekend (Saturday or Sunday), you can use a similar formula:

=WEEKDAY(C5,2)>5

Note:  Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.