Written by Jane Hames
How to prohibit weekends using Data Validation
The standard Data Validation offers some really useful options such as controlling the dates, text and numbers that can be entered in to cells. A few times recently, I have been asked how to set up data validation so that it only allows weekdays to be entered and sees weekends as invalid.
Firstly I need to explain the weekday function. This function looks at a date in a cell and tells you which day of the week that date falls on. Sunday is 1 and Saturday is 7. So, we can set up data validation so that it does not allow the 1st or 7th day of the week.
The week day function goes like this: =weekday(cell)
We could use the And function to test that the date entered into a cell is neither on day 1 or day 7. The And function goes like this: =And(test 1, test 2).
We would need two tests to test that the weekday is not equal to 1 or 7. It would look like this:
=And(weekday(cell)<>1,weekday(cell)<>7)
<> means not equal to.
In the example below, it shows how to set up custom Data Validation to ensure that only weekdays can be entered into cells D3:D7
Note: This tutorial assumes prior knowledge of Data Validation and basic functions.
