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