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

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.