Change the Color of Weekends in Excel

Change the Color of Weekends in Excel
Last Updated on 13/11/2023
Reading time: 3 minutes

How to Change the Color of Weekends with Conditional Formatting in 3 Easy Steps

  1. Create a test on the value of the day

    First, we must build a test with the WORKDAY function

  2. Create a custom conditional formatting rule

    Use the previous test as a custom rule

  3. Change the color of Weekends

    When the test is TRUE, your test will apply a specific color

Automatic Calendar

To explain how to change the color of weekends, let's start with this calendar. Each day is the result of a formula with the DATE function.

Automatic Calendar where each day is a formula

You can find more information about how to build a dynamic calendar in this article, or this one if you want to save the values for each month.

Step 1: Create the test for the weekend

When you create your custom rules for conditional formatting, the most important is to create your logical test. Here, the logical test is quite simple. The WEEKDAY function returns the day value in a week from a date(1 is Sunday, 2 is Monday, ...)

=WEEKDAY(B1) => 1

But you can also add an argument to this function to start your week the Monday and then Saturday = 6 and Sunday = 7

=WEEKDAY(B1, 2) => 7

But what we want, it's a logical test to find if a day is a weekend or not. To convert the previous formula into a logical test, we simply have to write this

=WEEKDAY(B$1, 2)>5

Test to find the weekends

Step 2: Use your test as a conditional formatting rule

  1. Open the menu Conditional Formatting > New Rule
Menu Condtional Formatting New Rule
  1. In the next dialog box, select the menu Use a formula to determine which cell to format (1)
Menu to create a custom conditional formatting rule

Step 3: Copy the formula of the test

In the text box (2), copy the logical test created in step 1.

Copy the logical test in the textbox to change the color of weekends

Note: In this case, you must lock only the reference of the rows in order to stay always on the date's row (row 1).

Step 4: Customize the color when the test is TRUE

The last step is to customize the format of your cell.

  1. Click on the Format button (3) to create your custom color when the test will be TRUE.
  2. Select the tab Fill
  3. Choose one color in the list
  4. Validate by clicking on OK
Select the color to apply to weekends

Step 5: Apply the rule to a range of cells

At this step, the conditional formatting is for only one cell. To extend the range of cells for this rule we must open Conditional Formatting> Manage Rules

Menu Conditional Formatting Manage Rules
  1. Then, select This Worksheet to see the worksheet rules instead of the default selection.
  2. Then in the Applies to section, change the range corresponding to your initial selection when creating your rules to extend it to the whole column.
  3. And now, all the dates equal to a weekend have a different color 😎😍😃
All weekends have a specific color different of the other days of the week

To understand why we have used only one $ in the formula, you can read this article to color an entire row with conditional formatting.

Change the color of the public holidays.

You can also highlight the public holidays with the COUNTIF function. The technique is explained in this article.

Change the color of weekends and public holidays

1 Comment

  1. Trudy
    17/04/2019 @ 17:27

    please help me. I have been working on this for 4 hours. I need to know how to write formula to highlight current month and next month in yellow, and highlight a month or any month that is out of date for the month in red. i am tracking upcoming license plate renewals. I want a two month warning and any month past due in red. I prefer to use an icon but a cell highlight is fine.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Change the Color of Weekends in Excel

Reading time: 3 minutes
Last Updated on 13/11/2023

How to Change the Color of Weekends with Conditional Formatting in 3 Easy Steps

  1. Create a test on the value of the day

    First, we must build a test with the WORKDAY function

  2. Create a custom conditional formatting rule

    Use the previous test as a custom rule

  3. Change the color of Weekends

    When the test is TRUE, your test will apply a specific color

Automatic Calendar

To explain how to change the color of weekends, let's start with this calendar. Each day is the result of a formula with the DATE function.

Automatic Calendar where each day is a formula

You can find more information about how to build a dynamic calendar in this article, or this one if you want to save the values for each month.

Step 1: Create the test for the weekend

When you create your custom rules for conditional formatting, the most important is to create your logical test. Here, the logical test is quite simple. The WEEKDAY function returns the day value in a week from a date(1 is Sunday, 2 is Monday, ...)

=WEEKDAY(B1) => 1

But you can also add an argument to this function to start your week the Monday and then Saturday = 6 and Sunday = 7

=WEEKDAY(B1, 2) => 7

But what we want, it's a logical test to find if a day is a weekend or not. To convert the previous formula into a logical test, we simply have to write this

=WEEKDAY(B$1, 2)>5

Test to find the weekends

Step 2: Use your test as a conditional formatting rule

  1. Open the menu Conditional Formatting > New Rule
Menu Condtional Formatting New Rule
  1. In the next dialog box, select the menu Use a formula to determine which cell to format (1)
Menu to create a custom conditional formatting rule

Step 3: Copy the formula of the test

In the text box (2), copy the logical test created in step 1.

Copy the logical test in the textbox to change the color of weekends

Note: In this case, you must lock only the reference of the rows in order to stay always on the date's row (row 1).

Step 4: Customize the color when the test is TRUE

The last step is to customize the format of your cell.

  1. Click on the Format button (3) to create your custom color when the test will be TRUE.
  2. Select the tab Fill
  3. Choose one color in the list
  4. Validate by clicking on OK
Select the color to apply to weekends

Step 5: Apply the rule to a range of cells

At this step, the conditional formatting is for only one cell. To extend the range of cells for this rule we must open Conditional Formatting> Manage Rules

Menu Conditional Formatting Manage Rules
  1. Then, select This Worksheet to see the worksheet rules instead of the default selection.
  2. Then in the Applies to section, change the range corresponding to your initial selection when creating your rules to extend it to the whole column.
  3. And now, all the dates equal to a weekend have a different color 😎😍😃
All weekends have a specific color different of the other days of the week

To understand why we have used only one $ in the formula, you can read this article to color an entire row with conditional formatting.

Change the color of the public holidays.

You can also highlight the public holidays with the COUNTIF function. The technique is explained in this article.

Change the color of weekends and public holidays

1 Comment

  1. Trudy
    17/04/2019 @ 17:27

    please help me. I have been working on this for 4 hours. I need to know how to write formula to highlight current month and next month in yellow, and highlight a month or any month that is out of date for the month in red. i am tracking upcoming license plate renewals. I want a two month warning and any month past due in red. I prefer to use an icon but a cell highlight is fine.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *