Pivot Table Conditional Formatting Weekend Dates

How to set up Excel pivot table conditional formatting to highlight number values that are connected to weekend dates in the row labels.

Highlight Weekend Data in Pivot Table

You can use conditional formatting in Excel, to highlight specific data, such as months with high sales numbers.

In this example, conditional formatting is applied to the data in a pivot table. This pivot table has workplace safety data, from a fictitious company. You can get the sample Excel file on my Contextures site in the Download section, at the end of this page.

The conditional formatting rule highlights:

• data for the number of incidents (column B)
• if they are connected to weekend dates, shown in the Row labels (column A)

Three Main Steps

There are 3 main steps in setting up this pivot table conditional formatting, and the details for each step are shown in the sections below:

-- 1) Create Formula for Weekend Dates

-- 2) Add Conditional Formatting Rule to Pivot Table

-- 3) Adjust Conditional Formatting Rule

1) Create Formula for Weekend Dates

Before using an Excel function in a conditional formatting rule, it can be helpful to test that function in a worksheet formula.

• On the worksheet, it's easier to spot any problems with the formula, and fix them on the spot.
• Later, when the worksheet formula works correctly, build a similar formula in the conditional formatting rule.

To test the formula on the worksheet, I did these steps:

--a) Test simple WEEKDAY function

--b) Formula to test for weekend dates

--c) Add 2nd argument to WEEKDAY function

--d) Shorter formula to test for weekend dates

a) Test Simple WEEKDAY Function

To find out which dates occur on a weekend, you can use the Excel WEEKDAY function – it returns a number, based on a date.

We'll use the WEEKDAY function in the conditional formatting rule, and test it on a worksheet first.

• NOTE: If you are familiar with the WEEKDAY section, and its return_type options, you can skip to the next section - Add Conditional Formatting Rule to Pivot Table

Get the Weekday Number

In this example, a date is entered in cell B3, and formatted as "ddd, mm d", so the weekday name is visible.

In cell B6, enter the following formula, to find the weekday number for that date.

• =WEEKDAY(B3)

The default numbering for the WEEKDAY function is from 1 (Sunday) to 7 (Saturday), shown in the screen shot below.

• date in cell B3 is a Wednesday
• result of WEEKDAY formula is 4

b) Formula to Test for Weekend Date

In the numbered list of weekdays, shown above, the weekend day numbers are 1 (Sunday) and 7 (Saturday).

On another worksheet, I've entered a list of dates to test, in cells B3 to B9.

In columns C, there is a formula that combines the OR function, with two WEEKDAY functions, to test for the numbers 1 and 7. This formula is in cell C3, and copied down to C9:

• =OR(WEEKDAY(B3)=1,WEEKDAY(B3)=7)

That formula works, and shows a TRUE for each weekend date, but we can improve it, with a small change.

See how to make a simpler formula, in the next section.

c) Add 2nd Argument to WEEKDAY

In the WEEKDAY function syntax, there are 2 arguments:

• serial_number: a number representing a valid Excel date
• return_type: (optional) Number from a specified list of weekday order options

Return Type Options

The return_type argument controls how the weekdays are numbered, and you can see the numbered list of options in the screen shot below.

Return Type Notes:

• In older versions of Excel, only options 1, 2 and 3 are available fro.
• If you omit return_type argument, default return_type (1) is used

Return Type 2

The conditional formatting rule should check for weekend dates, and that will be easier, if the WEEKDAY function uses return_type 2: Numbers 1 (Monday) through 7 (Sunday)

Return type 2 puts the two weekend days together, at the end of the list:

• 1 - 5 -- non-weekend days
• 6 - 7 -- weekend days

The WEEKDAY number can test if a date has a weekday number of greater than 5

d) Shorter Formula Test for Weekend Date

To test for weekend dates:

• add 2 as the second argument in the WEEKDAY function
• test if that result is greater than 5

Here's the revised formula in cell C3, to check if the weekday number is greater than 5:

• =WEEKDAY(B3,2)>5

That's the formula to use for the conditional formatting rule

2) Add Conditional Formatting Rule to Pivot Table

Next, here's how to use that WEEKDAY test formula to highlight weekend data in a pivot table. We want to highlight the

• number of incidents in column B
• if the date in column A falls on a weekend

Add Conditional Formatting

Follow these steps to apply the weekend highlighting in the pivot table:

• Select all cells where conditional formatting should be applied, cells B5 to B20 in this example.
• On the Excel Ribbon, click the Home tab
• Click Conditional Formatting, and in the drop down menu, click New Rule
• The New Formatting Rule dialog box opens
• At the top, in the Select a Rule Type section, click Use a formula to determine which cells to format.
• In the Formula box, enter the following WEEKDAY formula, referring to cell B5 -- the first cell with Incidents data:
  • =WEEKDAY($B5,2)>5
• Click the Format button, and select a Fill colour, for the weekend highlighting
  • For this example, light orange fill was selected
• Click OK to close the Format Cells dialog box.
• Click OK to close the New Formatting Rule dialog box.

The data for weekend dates are highlighted in the Incidents column.

WARNING: There's one more important step, so be sure to read the next section!

3) Adjust Conditional Formatting Rule

The conditional formatting looks good when you apply it, but if you change the pivot table layout later, or add new data, the correct cells might not be formatted.

Watch this video to see the steps for applying conditional formatting to pivot tables cells. Then adjust the rule, so new cells are formatted if the pivot table layout changes.

The written steps for changing the pivot table conditional formatting rule are below the video.

Change Pivot Table Conditional Formatting Rule

Be sure to complete this final step, after adding conditional formatting to pivot table cells.

Follow these steps to adjust the conditional formatting rule, so it refers to the pivot fields, instead of a specific range of cells

1. Select a cell in the pivot table where you applied conditional formatting
2. Next, on the Ribbon’s Home tab, click Conditional Formatting
3. In the drop down menu, click Manage Rules
4. Click on the WEEKDAY test rule that you want to change,
5. Click the Edit Rule button, to open the Edit Formatting Rule window.
6. In the Apply Rule To section, click on the 3rd option, which refers to the pivot field names
• All cells showing “Incidents” values for “Date”
7. Click OK, to apply the revised rule

Now, if the pivot table layout changes, or if more data is added, the correct cells will continue to be highlighted.

Compare Years in Multiple Pivot Tables

The workplace safety data sample file has 3 years of data in its records. If you want to compare the weekend data for different years, the easiest solution is to make copies of the completed pivot table, and line them up across a worksheet.

• NOTE: This step is not required -- it's an optional enhancement, so you can easily compare weekend data across multiple years.

To see the weekend incidents over a three year date range comparison, follow these steps:

• Copy the original pivot table
• Paste a copy into columns D:E
• Paste another copy into columns G:H
• Choose a year from the drop down in the Report Filter for each pivot table
• Select the same month number in each pivot table
  • Tip: Insert a pivot table Slicer for the Month field, and connect it to all 3 pivot tables
• The weekend highlighting adjusts in each pivot table, based on the incident dates for that year.

Download the Workbook

Workplace Safety: Click here to get the workplace safety data file. The file contains the safety data used in this example, and you can follow the steps above, to set up the pivot table conditional formatting. The zipped Excel file is in xlsx format, and does not contain any macros.

More Tutorials

Pivot Table Conditional Formatting

Conditional Formatting Basics

Conditional Formatting Examples

Conditional Formatting Data Bars