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:

=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

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

Why choose Glide?

Our Unique Approach

Our Unique Approach

We work on a bespoke, requirements-led basis rather than offer a set range of courses. We will work with you to identify your specific requirements and objectives before any training is delivered. That way you can be sure that training will always deliver exactly what you're looking to achieve.

View details »

Our Low Prices

Our Low Prices

We deliver all our on-site training at our customers' premises. Not only does that avoid any travel inconvenience for your team, but also it means we don't have the overheads required to maintain a dedicated training centre - and in turn we are able to pass these savings on to you.

View details »

Our Expertise

Our Expertise

Our trainers have designed and delivered IT training for a wide range of organisations and people including accountants, lawyers, data analysts, sales consultants and buyers. Clients benefit from our our ability to find creative solutions to their problems based on many years of experience.

View details »

Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo
Balderton Logo
Kreston Reeves Logo
National Institute of Health Research
England Rugby
Caravan and Motorhome Club Logo
Bird and Blend Logo
Ropetackle Logo
RBLI Logo
Farnborough Airport Logo
Kroll Logo
The Apprenticeship College Logo
Shoreham Port Logo
Adur and Worthing Councils Logo