Data validation allow weekday only

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)

April 14, 2025 · 1 min · 81 words · Jennifer Maynard

Table of Contents

Explanation

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

To get a number for a Monday-based week, thereturn_typeargument for WEEKDAY is provided as 2.

The WEEKDAY result is then compared to 6.

Excel formula: Data validation only dates between

Any value less than 6 is a weekday, so the expression returns TRUE and validation succeeds.

The TODAY function takes no arguments.

you’re able to format the value returned by TODAY with a datenumber format.

Excel formula: Data validation date in next 30 days

If you need current date and time, use…

Excel formula: Data validation date in specific year

Excel formula: Data validation allow numbers only

Excel formula: Data validation allow text only

Excel YEAR function

Excel TODAY function