Go Back

How to Perform Excel Data Validation Using the WEEKDAY Function

Data Validation allows or restricts users to enter a certain type of data in highlighted cells ranges in Excel. Excel has various built-in rules to control the entry of text, numeric, dates and time values in cells. Talking about date values, data validation has many built-in rules to allow dates entry in cells. Moreover, you can create your own Data Validation rule using a custom formula option in Excel.

How to setup Data Validation to allow Weekday dates only in Excel is a very important option to deal with. You can create your own rule to use Data Validation to allow weekday dates only with a custom formula option based on WEEKDAY function. This allows users to enter only weekdays (Monday to Friday) and takes weekend days (Saturday and Sunday) entry as invalid.   

Data validation in Exel using the WEEKDAY function

The Excel WEEKDAY function returns a number representing the day of the week, given a date value. Using this function, you can determine what is the day of the week for a given date value as per syntax of WEEKDAY function. In this way, you can easily set up a rule via data validation to allow weekday dates only in the selected range of cells.

The syntax of WEEKDAY function

WEEKDAY( serial_number, [return_value] )

serial_number: Enter date value as a serial number or a date in quotation marks.

return_value: (Optional). This argument determines the day to use as the first day of the week.

For example, if you enter this argument as “1” or omit this argument, then function by default returns a number from 1 (Sunday) to 7 (Saturday). If you enter this argument as “2”, then it returns a number from 1 (Monday) to 7 (Sunday).

Data Validation to allow Weekday dates only

Suppose you want to list the holiday dates in range B2:B7 and you want to restrict the entry of date values that fall in weekend days (Saturday and Sunday). In order to do that you need to use WEEKDAY function as the custom formula to set up data validation to allow Weekday dates only on the selected range of cells.

Following two formulas can be used in custom formula option in Data Validation feature in Excel. First, you need to select cells range B2:B7 and go to Data tab > Data Validation > Select Custom in Allow list. You can enter any of formulas listed below in Formula area and press the OK button.

Formula 1:

Enter following formula in Formula area of Data Validation dialog box. If you want to return Monday is the first day of the week, then you must enter “2” in the return_value argument.

=WEEKDAY(B2,2)<6

This formula creates a custom rule of Data Validation to allow weekday dates only from Monday to Friday where formula returns number from 1 to 5 and logical test (<6) return TRUE for given date values.

Formula 2:

Enter following formula in Formula area of Data Validation dialog box. If you don’t want to mention return-value argument, then WEEKDAY function returns the number from 1 (Sunday) to 7 (Saturday).

=AND(WEEKDAY(B2)<>1,WEEKDAY(B2)<>7)

This formula creates a custom rule that restricts the entry of date value for which WEEKDAY function returns number 1 and 7, and setup data validation to allow weekday dates only in cells B2:B7.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I was working with pivot tables and have a good understanding of how to build. One problem Ive run into and cant seem to solve is the dates in my filters. One of my filters, The Revised Promise Date and Actual Shipped Dates shows the detail date, IE 3/10/17, and allows me to filter. The dates I need like this are using the Original Promise and Need Dates as filters, those only allow me to filter by Month.
Solved by C. J. in 16 mins
I'd like this formula for a gantt chart to only display weekdays... =Project_Start-WEEKDAY(Project_Start,1)+2+7*(Display_Week-1)
Solved by O. S. in 26 mins
Hello I am trying to count the number of unique mondays in a data set. I am able to count the number of unique dates or count the number of mondays, but not able to combine the criteria. The only work around I found was to compare dates (if they are in sequential order) and run a WEEKDAY() formula, but I would prefer one step to do the calculation.
Solved by D. A. in 20 mins

Leave a Comment

avatar