Allow Weekend Dates Only in Excel

Allow Weekend Dates Only in Excel

With Excel Data Validation, you can add rules to a data entry sheet, and control what people put in the cells. In today's example, we'll set up a cell that only allows you to enter a weekend date. Just remember that Data Validation isn't foolproof, and people can find ways around your rules.

Weekend Date Challenge

Here's what we want in our data entry sheet. People can enter a date in cell B2, but we want them to enter weekend dates only.

If they put a Monday to Friday date in cell B2, a warning message should appear, and block that date from being entered.

Weekend Dates Only

Data Validation Drop Downs

One way to make sure that people make valid entries in a cell, is to set up a drop down list, and only allow those items. That's handy for a short list of items, such as employee names, or product categories.

A drop down list wouldn't be a good solution in this case though. We'd need to make a list of all possible weekend dates, and who has time for that?

Data Validation for Dates

Another Data Validation option is its set of built-in  Date Rules.

You can use those rules if you want to restrict dates  to a specific date range. Choose an operator from the drop down list, such as Greater Than, or Not Equal To.

dateoptions01

Then, fill in the date boxes that appear for the selected option. For example, choose Between, and set a start date and end date for the valid date range.

daterange02

Data Validation Custom Rule

Unfortunately, none of those built-in date options let you limit dates to specific days of the week.

To do that, you can set up a Data Validation Custom Rule.

weekendsonly03

Next, you'll need to enter a formula for the custom rules, to tell Excel that only weekend dates are allowed.

weekendsonly06

Identify the Weekday

I like to test formulas on the worksheet, before adding them to the Data Validation custom rule settings.

In the screen shot below, the date in cell B2 is Thursday, December 13th, formatted to show the weekday name.

There are 7 days in a week, and with the Excel WEEKDAY function, we can get a number for the weekday that a date falls on.

Here is my test formula, in cell B4:

=WEEKDAY(B2)

weekendsonly05

The result is 5 – the weekday number for Thursday.

WEEKDAY Numbering

My formula uses the default setting for WEEKDAY, which numbers the days from Sunday (1) to Saturday (7).

So, we could make a rule that only allows dates with a weekday number of 1 (Sunday) OR 7 (Saturday).

=OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7)

weekendsonly08

The date is B2 is not a Sunday or Saturday, so the formula result is FALSE. That date would not be allowed.

WEEKDAY Function Options

Instead of checking for 1 and 7, we could make a change to the WEEKDAY formula, so that we only have to do one check.

There is an optional argument for WEEKDAY – return_type. If you omit that argument, the default (1) is used.

weekendsonly07

Change the Return Type

If we use 2 as the return_type, the numbers start with Monday as 1. That puts Saturday and Sunday together at the end of the list, with numbers 6 and 7.

That lets us create a simpler formula – we can check for numbers greater than 5 (Friday).

=WEEKDAY(B2,2)>5

With the Thursday date in B2, the formula returns FALSE – it is not a valid weekend date.

weekendsonly09

If I change the date in B2 to December 15th (a Saturday), the result is TRUE. That is a weekend date, and would be allowed.

weekendsonly10

Create the Data Validation Custom Rule

After you've tested the formula on the worksheet, and it works correctly, set up the Data Validation Custom Rule.

  • Select cell B2, and on the Data tab, click Data Validation
  • From the Allow drop down, choose Custom
  • In the Formula box, type the formula that you tested:  =WEEKDAY(B2,2)>5
  • To show an error message, click the Error Alerts tab and set up an Error message
  • Click OK, to apply the custom rule

Data Validation Custom Rule

Test the rule in cell B2, by entering a few weekend dates, and non-weekend dates.

More Custom Rules

Go to my Contextures website, to see more Custom Rules for Data Validation.

There is a sample file that you can download, with those examples.

_______________________

Allow Weekend Dates Only in Excel

Allow Weekend Dates Only in Excel

_______________________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.