Home > Formulas > Date > Weekend Dates

Weekend Dates in Excel

Examples show how to work with weekend dates in Microsoft Excel. Highlight rows with weekend dates, prevent or allow entry of weekend dates. Find project start and end dates, with no weekend days. Excel weekend formula to 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:

• serial_number - Dates are stored as serial numbers in Excel, so you could type a number in the formula, or refer to a cell that contains a valid date.

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

• Default numbering goes from Sunday (1) to Saturday (7)
• Date in cell B4 is a Sunday, so the formula result is 1

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

WEEKDAY Numbering

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:

• return_type- A number from the list of valid option numbers, shown in the screen shot below.

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.

Return_Type Notes

Here are a couple of things to keep in mind, when using the WEEKDAY function

• No 2nd argument: If you omit the optional return_type argument, the default return_type (1) is used. It numbers the days from Sunday (1) to Saturday (7)
• Old Versions: Originally, there were only 3 return types - 1, 2, and 3
• Current Versions: In Excel 2007, another 7 options were added: numbers 11 through 17

Identify Weekend Dates

If you want to identify weekend dates that are Saturday and Sunday, the quickest solution is to use return_type 2.

• Note: To identify different days as weekend dates, use the formula shown in the Other Days section below

For Saturday and Sunday weekends, return_type 2 has the following advantages over the other options:

• Days are numbered Monday (1) to Sunday (7)
• Weekends are together, at the end of the week, Sat (6) and Sun (7)
• To check for weekend dates, you can use a single test:
  • Is weekday number greater than 5?
• Return_Type 2 is available in all versions of Excel

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

• =WEEKDAY(B7,2) > 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,

• a restaurant is closed on Monday and Tuesday, when not too many people dine out
• a factory closes on Friday and Saturday, so workers can spend time with their families

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.

• To make the formula shorter, omit the optional return_type argument.
• In default numbering system, Monday = 2 and Tuesday = 3
• Formula in cell C5 checks if date is weekday number 2 OR 3
  • =OR(WEEKDAY($C$3)=2, WEEKDAY($C$3)=3)

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(WEEKDAY($C$3)=5, WEEKDAY($C$3)=6)

Or, maybe you work a 4-day week, and are closed Saturday (7), Sunday (1) and Monday (2).

• =OR(WEEKDAY($C$3)=1, WEEKDAY($C$3)=2, WEEKDAY($C$3)=7)

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

• First, select all the data in the sales list, but don't include the heading cells
  • Note: Cell A2 is the active cell, so the conditional formatting rule will refer to that cell

• On the Ribbon, click the Home tab
• Next, in the Styles group, click Conditional Formatting.
• In the list of conditional formatting options, click New Rule, to open the New Formatting Rule dialog box

Create the Rule

Next, follow these steps to create the weekend highlight rule:

• In the Select a Rule Type list, click Use a formula to determine which cells to format.
• The lower section, named Edit the Rule Description, changes, and shows a box where you can enter a formula - Format values where this formula is true
• In the Formula box, enter the following WEEKDAY formula, to check the date cell A2 (active cell on worksheet):
  • =WEEKDAY($A2,2)>5
  • Note: Column is locked ($A2), and row is NOT locked with a $
• Next, click the Format button
• In the Format Cells dialog box, go to the Fill tab
• Select a Fill colour, or other formatting options, then click OK.
• Click OK to close the New Formatting Rule dialog box, and to apply the new 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.

• With that option, Saturday is day 6, and Sunday is Day 7
• Our rule will only allow weekdays with numbers LESS THAN 6

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:

1. Select cell B2
2. On the Ribbon's Data tab, click Data Validation
3. In the Data Validation dialog box, go to the Settings tab
4. From the Allow drop down, choose: Custom
5. In the formula formula box, enter this formula, to check for non-weekend dates:

• =WEEKDAY(B2,2) <6

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:

• In the Data Validation dialog box, go to the Error Alert tab.
• In the Title box, type a short heading for the error message, such as "No Weekends"
  • This text will appear in bold print at the top of the custom error message.
• Type a short message in the Error message box, such as "Weekend dates are not allowed"
  • Clearly explain what the data validation rules are for this cell
  • Error message text limit is 225 characters
• To apply the Error Alert settings, click OK

Test the Data Validation

Click the OK button, to apply the data validation rule to the selected cell

Project Task Start and End Dates

In the video below, the WORKDAY function is used in column C, to calculate start and end dates for project tasks. Weekend dates are not included in the project, and the WORKDAY function does not count those days.

Find Task Start Dates

This is the formula in cell C6, to calculate the start date for the first task, based on the deadline date, and the number of days work required:

• =WORKDAY(MAX(C5,$B$2), SUM(D5), HolidayList)

Make a Gantt Chart

Next, a simple Gantt chart is created from those task start dates, that the WORKDAY function calculated.

The written steps, and the sample file for this video, are on the Excel Gantt Chart Project Plan page.

Download Sample File

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.

More Tutorials

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