## Highlight weekends and holidays using Conditional Formatting in #Excel

It is not uncommon to work with dates in Excel.  Be it a Gantt chart for project management, a roster, or simply a calendar, we would like Excel to highlight non-working dates.  One of the most common tasks should be highlighting weekends and public holidays.  It could be a tedious manual task when you need to maintain it on monthly basis.  The good news is this tedious, boring task can be done by conditional formatting with ease.

### The formula to identify day of week

On top of my mind, there are two functions to achieve this:  TEXT and WEEKDAY.

Indeed, I prefer to using TEXT function as the result it returns is more intuitive.

=TEXT(\$A2,"DDD")
'where A2 resides a date (input as numeric date not text)

See below to find out what I meant “more intuitive”.

Agree?

### The formula for the conditional formatting to highlight weekends

We will need a formula to set up the condition.  And the formula is also intuitive:

=TEXT(\$A2,"DDD")="Sat"
'Note: The column is an absolute reference while the row is relative - \$A2

To set up conditional formatting with this formula, follow these steps:

1. Select the range (A2:G11 in our example)
2. Go to Home tab
3. Conditionally Formatting
4. New Rule…

When the “New Formatting Rule” dialog box opens:

1. Select “Use a formula to determine which cells to format”
2. Input the formula =TEXT(\$A2,”DDD”)=”Sat”
3. Set the Format you want (when this condition is met)

There we go…

The row of Saturday(s) is highlighted. Yeah!

To highlight also Sundays, repeat the above steps with the following formula:

=TEXT(\$A2,"DDD")="Sun"
'Note: The column is an absolute reference while the row is relative \$A2

Then you should get the following result:

Now let’s examine what conditions have been applied:

You will see:

1. Rule (applied in order shown): There are two rules – based on the formula we set (note: if you have more rules, you will also see them here)
2. Format: The Format set when the rule is met
3. Applies to: The range where the rule and format are applied to

Tip: Since we want to highlight both Saturdays and Sundays with the same format, we may combine the two rules into one by using the following formula:

=OR(TEXT(\$A2,"DDD")="Sat",TEXT(\$A2,"DDD")="Sun")
'Note: The column is an absolute reference while the row is relative \$A2

The function OR instructs Excel to return TRUE when either one of the conditions is met.

### To highlight public holidays

To achieve this, we have to list the public holidays on the spreadsheet.  (Different regions have different public holidays… and the real obstacle is different industries may have different holidays, so Excel doesn’t have a built-in function to identify public holidays).

In our example, the Public Holidays is listed on \$I\$2:\$I\$18

### The formula for the conditional formatting to highlight public holidays

=COUNTIF(\$I\$2:\$I\$18,\$A2)
'Note: Pay attention to \$

This formula counts the occurrence A2 (date) in the range \$I\$2:\$I\$18 (public holidays).  In other words, if it is a public holiday (as listed under PublicHolidays), it returns 1 (TRUE).  If it is not a public holiday (not listed under PublicHolidays), it returns 0 (FALSE).  This is exactly what we need to set up the conditional formatting. 🙂

Let’s add this rule by repeating the steps with the formula:

(Note: A different format is applied for Public Holiday to demonstrate another concept, which I am going to explain)

Here we go!

Let’s test it by changing the dates in column A

Yessssss… it’s working like a charm! 🙂

### Question: What if a public holiday happens to fall on Sat?  Which formats would it display?

For demonstrating purpose, I’ve marked 2nd of March (Sat) as a public holiday.  And you may expect it be highlighted as public holiday, which is true.

Let’s examine the order of the rules set in conditional formatting:

Please pay attention to what is highlighted in Yellow in the screenshot above.

#### What if we switch the order of the two rules?  Watch this:

See?!  The order of rules makes a difference when there are overlapping.  First-come-first-served. 🙂

### Conditional Formatting with formula could be tricky (or difficult), especially when the data layout is bad

because you have to very clear and careful on the “applied to” range and the absolute/relative references set in the formula.  Otherwise, it won’t work and can be quite confusing, if not frustrating.  😛

In the next post, I will talk about a case that you will see a slight change in the layout (see below) would make a difference in the set up of the conditional formatting.

where a blank row is inserted between each data for whatever reasons…

Stay tuned!

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Format and tagged , , , . Bookmark the permalink.

### 3 Responses to Highlight weekends and holidays using Conditional Formatting in #Excel

1. David N says:

You could also use a fancier OR function to cover both Saturday and Sunday at once. And it doesn’t even require Ctrl+Shift+Enter despite the use of the {…} braces to create an array constant.
=OR(TEXT(\$A2,”DDD”)={“Sat”,”Sun”})

Like

• MF says:

Thank you David for your pro tip. I know it should work… but somehow not working on Excel 2016…
Haven’t tried other versions. Any ideas?

Like

• David N says:

It seems you’re correct. The error says that array constants can’t be used in conditional formatting rules. So I guess you could still use my formula on the worksheet itself, but your formula for handling Saturday and Sunday separately would be the better choice for conditional formatting.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.