How To Highlight Weekends In Excel?

When working with dates in Excel, we can highlight weekend dates to visually distinguish them from weekdays or to emphasize their non-working status.

The highlighting can be handy in situations such as when creating project timelines or work schedules.

By highlighting weekends, it’s easier to see which days fall on weekends and which are workdays.

This tutorial shows techniques for highlighting weekend dates in Excel using conditional formatting. Additionally, it explains how to highlight holidays and any particular days.

Highlight Weekend Dates in Excel

You can use conditional formatting to highlight weekend dates in Excel. The conditional formatting feature allows you to set rules that apply formatting to cells based on their contents.

For example, you can set a rule that applies a background color to all cells that contain weekend dates.

When highlighting weekend dates in Excel, you can highlight the particular cells containing the weekend dates or the entire rows having the weekend dates.

Method #1: How to Highlight Only Particular Cells Containing Weekend Dates

Let’s consider the dataset below showing the orders placed with a particular soft drinks manufacturer on various dates.

We want to apply conditional formatting to highlight the cells in the Order Date column containing dates that fall on Saturdays and Sundays.

We can use the following steps to do this:

1. Highlight the cell range A2:A20 containing the dates.

2. On the Home tab, open the Conditional Formatting drop-down on the Styles group and choose New Rule.

3. On the New Formatting Rule dialog box, select the Use a formula to determine which cells to format option on the Select a Rule Type box:

4. Enter the following formula on the Format values where this formula is true box:

=WEEKDAY(A2,2)>5

5. Click the Format button next to the Preview box.
6. On the Format Cells dialog box that pops up, open the Fill tab, choose a background color from the color palette (in this example, we have chosen light green), and click OK.

7. Click OK on the New Formatting Rule dialog box.

The cells with weekend dates are highlighted in light green.

Explanation of the Conditional Formatting Formula

=WEEKDAY(A2,2)>5

Note: We have applied this formula in column G of the example dataset so that we can explain how it works.

• =WEEKDAY(A2,2) – In this part of the formula, the WEEKDAY function uses the date in cell A2 as the serial_number argument and the number 2 as the return_type argument to return a number identifying the day of the week of the target date in cell A2.

The number 2 return type specifies 1 for Monday, 2 for Tuesday, and so on. The numbering sequence means that Saturday and Sunday are identified by the values 6 and 7, respectively.

In this case, the function returns the value 3 for Wednesday, as shown below:

• =WEEKDAY(A2,2)>5 – This formula checks if the day of the week for the date in cell A2 is greater than 5 (which means it’s a Saturday or Sunday).

If the day of the week is greater than 5, the formula returns TRUE, and the conditional formatting feature highlights the cell containing the date. Otherwise, it returns FALSE, and the conditional formatting feature does nothing to the cell containing the date.

In this example, the formula returns FALSE because the date in cell A2 is not a weekend date.

Notice that the formula returns TRUE in cells G4 and G5 because the target dates in column A are weekend dates. Therefore, the cells containing the dates are highlighted in light green.

Also read: Highlight Cell If Value Exists in Another Column in Excel

Method #2: How to Highlight Rows Containing Weekend Dates

Suppose we have the following dataset showing the orders placed with a particular soft drinks manufacturer on various dates.

We want to apply conditional formatting to highlight entire rows in the dataset containing dates that fall on Saturdays and Sundays.

We can use the following steps to do this:

1. Select the entire data range where we want to highlight entire rows.

2. On the Home tab, open the Conditional Formatting drop-down on the Styles group and choose New Rule.

3. On the New Formatting Rule dialog box, select the Use a formula to determine which cells to format option on the Select a Rule Type box:

4. Enter the following formula on the Format values where this formula is true box:

=WEEKDAY($A2,2)>5

5. Click the Format button next to the Preview box.
6. On the Format Cells dialog box that appears, open the Fill tab, choose a background color on the color palette (In this example, we have chosen orange), and click OK.

7. Click OK on the New Formatting Rule dialog box.

Entire rows containing weekend dates have been highlighted in orange.

Explanation of Conditional Formatting Formula

=WEEKDAY($A2,2)>5

The formula uses absolute referencing for the column ($A) to lock it so that as the formula is copied across the data range, it does not change.

However, the row reference (2) is relative and will change as the formula is copied to different rows.

This formula checks if the day of the week for the date in cell A2 is greater than 5 (which means it’s a Saturday or Sunday).

If the day of the week is greater than 5, the formula returns TRUE, and the conditional formatting feature highlights the entire row containing the date.

Otherwise, it returns FALSE, and the conditional formatting feature does nothing to the row containing the date.

Note: If you want to highlight only those rows with Sunday dates on the example dataset, enter the following formula on the New Formatting Rule dialog box:

=WEEKDAY($A2,2)>6

When the final step of the conditional formatting process is applied, only rows with Sunday dates are highlighted, as shown below:

Also read: How to Highlight Dates Before Today in Excel?

Highlight Weekend Dates and Holidays

We can use conditional formatting to highlight the rows containing holiday dates and those with weekend dates.

Let’s consider the following dataset showing federal holidays in the USA. This dataset is contained in our workbook’s “Federal Holidays” worksheet.

We want to highlight the rows with holidays in our example dataset below, with the rows with weekend dates already highlighted.

We can use the following steps to do this:

1. Select the entire dataset.

2. On the Home tab, open the Conditional Formatting drop-down on the Styles group and choose New Rule.

3. On the New Formatting Rule dialog box, select the Use a formula to determine which cells to format option on the Select a Rule Type box:

4. Enter the formula below on the Format values where this formula is true box:

=ISNUMBER(VLOOKUP($A2,'Federal Holidays'!$B$2:$B$12,1,FALSE))

5. Click the Format button next to the Preview box.
6. On the Format Cells dialog box, open the Fill tab, choose a background color different from the color used to highlight the weekend dates (In this case, we have chosen light blue) on the color palette, and click OK.

7. Click OK on the New Formatting Rule dialog box.

The rows with holiday dates are highlighted in light blue. Notice that if the holiday is also a weekend, the new formatting rule overrides the previous one, and the row is highlighted in light blue.

Explanation of the Conditional Formatting Formula

=ISNUMBER(VLOOKUP($A2,’Federal Holidays’!$B$2:$B$12,1,FALSE))

Note: We use the formula in column G of the example dataset so that we can explain how the formula works.

• The VLOOKUP function looks for the date in cell A2 of the example dataset in column B of the holidays’ calendar on the “Federal Holidays” worksheet.  The function looks for the exact match and returns a number if it finds the date or the #N/A error if it does not.

Notice that in the lookup value,  the formula uses absolute referencing for the column ($A) to lock it so that as the formula is copied across the data range, it does not change. However, the row reference (2) is relative and will change as the formula is copied to different rows.

Additionally, the formula uses absolute referencing for the table array (column B of the holidays’ calendar) so that it is locked and does not change as the formula is copied down the column.

• The ISNUMBER function returns TRUE if the value returned by the VLOOKUP function is a number and FALSE if it is a #N/A error.

• If the value returned by the ISNUMBER function is TRUE, meaning the row has a holiday date, the conditional formatting feature highlights the entire row. Otherwise, it does nothing to the row.

Highlight Weekend Dates Along with Specific Days of the Week

We can apply conditional formatting to highlight particular days of the week. For example, highlight only Wednesday and Friday dates.

Suppose we have the following data range showing the orders placed with a particular soft drinks company.

We want to highlight only those rows with Friday and Wednesday dates.

We can use the following steps to do this:

1. Select the entire data range.

2. On the Home tab, open the Conditional Formatting drop-down on the Styles group and choose New Rule.

3. On the New Formatting Rule dialog box, select the Use a formula to determine which cells to format option on the Select a Rule Type box:

4. Enter the formula below on the Format values where this formula is true box:

=OR(WEEKDAY($A2,2)=3, WEEKDAY($A2,2)=5)

5. Click the Format button next to the Preview box.
6. On the Format Cells dialog box, open the Fill tab, choose a background (In this case, we have chosen yellow) on the color palette, and click OK.

6. Click OK on the New Formatting Rule dialog box.

All the rows with Wednesday or Friday dates are highlighted in yellow.

Explanation of the Conditional Formatting Formula

=OR(WEEKDAY($A2,2)=3, WEEKDAY($A2,2)=5)

The OR formula checks whether the WEEKDAY formulas return FALSE or TRUE. For example, the WEEKDAY formulas return TRUE if the date in cell A2 is either a Wednesday, identified by the value 3,  or a Friday, determined by the value 5.

If either of the WEEKDAY formulas returns TRUE, the OR function returns TRUE, and the conditional formatting highlights the entire row containing the target date.

If both WEEKDAY formulas return FALSE, the conditional formatting feature leaves the row as is.

This tutorial showed techniques for highlighting weekend dates in Excel using conditional formatting.

Additionally, it explains how to highlight holidays and any particular days.

I hope you found this Excel tutorial helpful. Do let me know your thoughts in the comments section.

Other Excel articles you may also like:

• Calculate Days Between Two Dates in Excel (Workdays/Weekends)
• Convert YYYYMMDD to MM/DD/YYYY in Excel
• Calculate Days Left in the Year (Formula)
• How to Get Total Days in Month in Excel?
• How to Apply Long Date Format in Excel?