Summary

Note: Excel has several built-in data validation rules for dates. This page explains how to create 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

Generic formula

=WEEKDAY(A1,2)<6

Explanation 

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 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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.