Glide Training Logo

How To Prohibit Weekends Using Data Validation


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:


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

data validation

Note: This tutorial assumes prior knowledge of Data Validation and basic functions.


previous post: How to create a variable drop down list in Excel

next post: Using The Windows Snipping Tool For Taking A Screen Shot / Screen Grab