Data validation allow weekday only

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar