How to work with weekend dates in Microsoft Excel. Highlight rows with weekend dates. Prevent or allow entry of weekend dates. Find Saturday and Sunday or different days for weekends.
Author: Debra Dalgleish
Get Weekday Number
For most of the examples below, formulas use the Excel WEEKDAY function, to check if a date falls on a weekend.
The WEEKDAY function has one required argument in its syntax:
Based on that serial number, WEEKDAY returns a number, for the date's day of the week. For example, in the screen shot below, there's a date in cell B4
So, if we want to highlight weekend dates, or prevent them from being entered in a cell, we can check for specific weekday numbers, such as 1 and 7
Although the default numbering for the WEEKDAY function goes from Sunday (1) to Saturday (7), you can tell Excel to use a different numbering system.
To specify a different numbering system, use the WEEKDAY function second argument, which is optional:
Don't worry though - you don't have to memorize those option numbers! The list of return type numbers will automatically appear, if you type a comma after the first argument.
Here are a couple of things to keep in mind, when using the WEEKDAY function
Identify Weekend Dates
If you want to identify weekend dates that are Saturday and Sunday, the quickest solution is to use return_type 2.
For Saturday and Sunday weekends, return_type 2 has the following advantages over the other options:
Saturday and Sunday Example
In the example shown below, the formula in column D uses 2 as its Return Type, so Days are numbered Monday (1) to Sunday (7).
The WEEKDAY function checks the date in column B
The weekday number is tested, with the greater than operator (>) to see if it is greater than 5
The result of that formula will be TRUE, if the weekday is a Saturday (6) or a Sunday (7)
In the screen shot below, the last two dates fall on weekend dates, so the formulas in cells D6 and D7 have TRUE as their result.
Other Weekend Days Example
Many businesses are closed on days that are NOT Saturday and Sunday, so the formula shown above will not identify their weekend dates. For example,
To identify weekend dates that are NOT Saturday and Sunday, you can create a formula with two WEEKDAY tests in it, instead of one test.
Other Days Example - Monday & Tuesday
In the example shown below, a business is closed on Monday and Tuesday. The WEEKDAY formula will check if a date falls on either of those days of the week.
On the worksheet, there's a date entered in cell B3. The WEEKDAY formula is in cell C5.
Adapt Formula for Different Days
You could adapt the formula, shown above, for any other days of the week
For example, use this formula if your business is closed Thursday (5) and Friday (6):
Or, maybe you work a 4-day week, and are closed Saturday (7), Sunday (1) and Monday (2).
Find the weekdays, and their default numbers, in the list shown below. Then, use those numbers in the WEEKDAY formula, along with the OR function.
Highlight Weekend Dates
For some data analysis, it might help if you highlight the rows where dates fall on a weekend. For example, in a list of daily sales numbers, colour the weekend rows, so they're easy to spot. That breaks the data into weekly "chunks", so it's easier to read and understand.
This video shows how to highlight the weekend dates with conditional formatting, using a rule with the WEEKDAY function. There are written steps below the video
Highlight Weekend Dates
In this example, the Excel file has a list with dates, and product sales numbers. In the steps below, you'll see how to highlight rows where the dates fall on a weekend.
You'll set up a conditional formatting rule, using the WEEKDAY function.
Add Conditional Formatting
To set up the conditional formatting for weekend dates, follow these steps
Create the Rule
Next, follow these steps to create the weekend highlight rule:
Weekend Date Highlighted
On the worksheet, the rows with weekend dates in column A are highlighted, with the fill colour that you selected - light green fill colour in the screen shot below.
With the green highlighting, it's easy to see the weekend sales data. Also, it's clear where each week begins and ends.
Block Weekend Dates
The Excel data validation feature lets you set rules for what can be entered in a worksheet cell.
To block people from entering weekend dates, create a data validation rule that uses the WEEKDAY function, as shown in the steps below.
For this rule, the example uses WEEKDAY with return_type 2 as the optional second argument.
Add Data Validation Rule
For this rule, we will allow dates that are NOT weekend dates. So, instead of checking if the weekday number is greater than 5, the rule will check for numbers LESS THAN 6 (Saturday)
Set Up Data Validation Rule
To set up the data validation rule in cell B2, follow these steps:
Add Error Message (Optional)
Instead of showing a default error message, if someone enters an invalid date, you can add a custom error message. To set that up, follow these steps:
Test the Data Validation
Click the OK button, to apply the data validation rule to the selected cell
Weekend Date Examples: To see how examples from this page, download the sample Weekend Date Examples workbook. The zipped Excel file is in xlsx format, and does not contain any macros.
Date Formula Examples -- Use formulas to find month name, weekday number, month star or end date. Many more examples.
WORKDAY Function Examples --Calculate project dates or upcoming workdays in Excel with WORKDAY function and WORKDAY.INTL function.
Excel Split Date and Time -- Get Date or Time value from cell with simple Excel formulas or built-in commands
Last updated: August 29, 2022 3:52 PM