Excel DATA VALIDATION using 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, Excel 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.   

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

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

 

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