The Excel WEEKDAY function takes a date and returns a number between 1 to 7 representing the day of the week. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. By using a custom formula based on the WEEKDAY function, you can ensure data validation allow weekday only.

**Formula**

`=`

`WEEKDAY(date,2)<6`

**Explanation**

The formula incorporating Excel WEEKDAY function to ensure data validation allow weekday only uses the following argument:

**Date** (required): It is the date of a particular month for which we need to check whether it’s a weekday or not.

This works as follows:

- This custom validation formula uses the WEEKDAY function to get a numeric value, 1 to 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 the validation is successful. If the weekday number is not less than 6, validation fails because the date is either a Saturday or Sunday.

**Example** 1

In this example, we have used the formula in such a way that data validation allows weekday only. For the same following formula is used using Excel WEEKDAY function,

`=`

**WEEKDAY(C5,2)<6**

This works as follows:

- The WEEKDAY result is compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and the validation is successful.
- If the weekday number is not less than 6, validation fails because the date is either a Saturday or Sunday. In such case, you’ll have to enter any other date.

* Figure 1. Example of Data Validation to check for weekday 1*

**Example 2**

Another example, here we have used the formula in such a way that data validation allows weekday only. For the same following formula is used using Excel WEEKDAY function,

`=`

**WEEKDAY(C5,2)<6**

This works as follows:

- The WEEKDAY result is compared to 6. Any value less than 6 is a weekday, so the expression returns TRUE and the validation is successful.
- If the weekday number is not less than 6, validation fails because the date is either a Saturday or Sunday. In such case, you’ll have to enter any other date.

*Figure 2. Example of Data Validation to check for weekday 2*

**Notes**

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

`=`

**WEEKDAY(date,2)>5**

## Leave a Comment