Conditional Formatting Date

Change the color of the weekends

Reading Time: 3 minutes

Weekends are always important when you create calendar. Let's see how to change the color of these days automatically.

The weekends have a different color automatically

Create your custom rule

In this article, we have seen how to create your custom rule and applied it as conditional formatting.

Which cells have a value lower than 15

With the same technique, we are going to see how to create the tests for the week-ends.

Highlighting weekends

Let's start from this calendar.

Build the test

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 function WEEKDAY returns the day value in a week from a date(1 is Sunday, 2 is Monday, ...)

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

Finally, to know if a day is a week-end of not, the test is

=WEEKDAY(B5, 2)>5

Use your test as a conditional formatting rule

  1. Open the menu Conditional Formatting > New Rule
Conditional formatting create a new rule
  1. In the next dialog box, select the menu Use a formula to determine which cell to format.

Copy your formula

Creation of a custom rule to highlight weekends

Note: In this case, you must lock the reference of the rows in order to stay on the date (row = 5)

Customise the color when the test is TRUE

Then, customise the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

  • Select the tab Fill
  • Choose one color

Apply the rule for a range of cells

At this step, the conditional formatting is for one cell only. So we must extend the range of cells for this rule.

Click OK, then open Conditional Formatting> Manage Rules

  1. Select This Worksheet to see theworksheet rules instead of thedefault selection.
  2. Then in the Applies to section, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.
Apply the rule for a range of cells

And now, all the dates equal to a weekends have a different color 😎😍😃

The weekends have a different color

Change the color of the holidays

If you want also to change the color of the holidays, the formula to build the test isn't the same. Explanations in this article.

Public holidays have a specific color

Related posts

What is a conditional formatting

Frédéric LE GUEN

Time Format in Excel

Frédéric LE GUEN

Weekly calendar 2020 in one formula

Frédéric LE GUEN

1 comment

Trudy 17/04/2019 at 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.


Leave a Comment