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.
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.
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.
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.
Next, you’ll need to enter a formula for the custom rules, to tell Excel that only weekend dates are allowed.
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)
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)
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.
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.
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.
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
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.
Prevent Invalid Dates With Excel Data Validation
Here’s another example of using data validation to control which dates can be entered in Excel. In this video, 3 different methods are used to validate dates.
- Specify a starting date and an ending date. (Date option)
- Show a drop down list of valid dates (List option)
- Create a rule in a custom formula (Custom option)
Written instructions, and the sample file, are on the Data Validation for Dates page, on my Contextures site.
_______________________
Allow Weekend Dates Only in Excel
_______________________
One thought on “Allow Weekend Dates Only in Excel”