Microsoft

Pro tip: Use Excel's conditional formatting to highlight holidays

Susan Harkins shows you how to visually identify holidays using Excel's conditional formatting feature.

Excel holiday information

Last year, I showed you a dynamic approach to using conditional formatting to highlight weekends in a monthly matrix-style calendar. In that article, "Use Excel's conditional formatting to highlight weekends," we created the sheet shown in Figure A. By separating the year, month, and day, you can reuse the same sheet. This month, we'll add a rule that highlights holidays.

If you want to build the original calendar from scratch, you can review the earlier article — or you can download the example .xlsx file. In that file, you'll find three sheets:

  • 2013 Matrix: The calendar from the 2013 article that highlights weekends
  • Holidays: A sheet of holiday dates that we'll create in this article
  • Holiday Conditional Format: The 2013 matrix that highlights weekends and holidays

Figure A

Figure A

Build on this earlier calendar that highlights weekends to also highlight holidays.

This technique relies on the NETWORKDAYS() function, which is available in Excel 2007 and later versions. There's no Excel 2003 (.xls) solution in this article for holidays.

The holidays

The original calendar is a simple monthly matrix that uses a conditional formatting rule to highlight the weekends. The flexible configuration splits the date components so you can reuse the calendar for any month and year. That flexibility complicates the addition of holidays. You'll have to enter a list of holidays, and as static date values, holidays will include a year. There's nothing wrong with that, but the holiday rule will work properly only when the calendar year (C3) and the years in your holiday dates match. It's a less than ideal setup, because you must update the holiday date values to match the calendar's year value. A better solution is to create a list of dynamic holidays. That way, the only time you have to update that list of holiday values is when you add or delete a holiday.

There's good news and bad news on this subject. We can use expressions to return the holiday dates based on the year in the original calendar, but we can't use the same expression for all of them. The list in Figure B is a list of recurring federal holidays in the US. You'll want to update yours to accommodate organization, civic, and cultural celebrations that apply to you. The dates we'll reference in the conditional formatting rule are in column F. Columns A and B aren't necessary but offer a bit of documentation. You can exclude them if you like.

Figure B

Figure B

Create a list of holidays.

Refer to Table A for the expressions. I'll explain the values (and lack of values) in columns C, D, and E later.

Table A

Table A

Holidays and expressions.

If you're not in the US, all or none of the expressions above might be of use. I'd like to be more internationally comprehensive, but that's just beyond the scope of a single article. The simplest expression works with holidays that fall on the same day each year:

=DATE(year,month,day)

We'll use it for four of our holidays that fall on the same day of the same month each year: New Year's, Independence Day, Veteran's Day, and Christmas. Those of you not in the US can probably use this simple expression to render many of your holidays.

The month and year arguments are literal values. Only the year argument is a reference — and in this case, it references B1, which returns the year from the original calendar. Remember, the calendar is on another sheet. You could put the calendar and holidays on the same sheet if you like. If C3 is empty, all of the expressions return the year 1900. You could add an error handling function if the visual clue of seeing 1900 isn't enough.

A second expression handles Martin Luther King Day, Labor Day, and Thanksgiving. (If you're not in the US, you might have holidays that fall on the same nth day of the same month.) This expression takes the following form:

=DATE(year,month,1+7*nthday)-WEEKDAY(DATE(year,month,8-dayofweek))

I wish I could credit all the Excel experts who've worked to perfect this expression, but I can't. Suffice it to say, this is not my original idea, and I thank those who came before me!

This expression relies on the literal values in columns C, D, and E, which represent the holiday's month as an integer, the day of the week (Sunday is 1, Monday is 2, Tuesday is 3, and so on), and the nth day of the week within the month (third Monday, first Monday, and fourth Thursday), respectively.

The first expression returns the last possible day of the month on which the holiday could occur — the 7*nthday evaluates that last possible day. The second expression is a bit of magic and returns the value necessary to return the right day as a value. (Clear as mud, right?)

Let's take a closer look by evaluating Martin Luther King Day:

=DATE(2015,1,1+7*3)-WEEKDAY(DATE(2015,1,8-2))
=DATE(2015,1,22)-WEEKDAY(DATE(2015,1,6))
=1/22/15-3
=1/19/15

The 3 and the 2 in the first expression are the nthday and day of the week values, respectively. Just accept it... it's magic.

There's only one expression left, the one for Memorial Day, which falls on the last Monday of September. This expression uses the following form:

=DATE(year,month,31)-WEEKDAY(DATE(year,month,31),3)

More magic — the logic of returning the last possible date and then subtracting the right value to return the last Monday is similar to the second expression, but it requires different math for the days.

The last step is to assign a range name to the holidays. You don't have to do this. You can use a literal cell reference, but using a range name lets you expand the holidays without updating the formatting rule that will apply later. Use your favorite range name method to apply the name HolidayTable to F4:F11. (I usually select the range and enter the name in the Name Box in the top-left corner.)

The rule

With the holiday table in place, you're ready to add a new conditional formatting rule to the calendar. This rule will rely on the NETWORKDAYS() function to evaluate full date values using the following form:

=NETWORKDAYS(DATE(year,month,day),DATE(year,month,day),holidays)

To clarify how the rule works, we'll enter this expression at the sheet level and refer to it in the rule's formula. You could enter the entire expression at the rule level if you don't want to display the results of the expression — 0s and 1 — in the sheet. The 0 value indicates a Saturday, Sunday, or holiday. The 1 value represents weekdays that aren't holidays.

Now, enter the following expression in C4 and copy it across the calendar (to AG4):

=NETWORKDAYS(DATE($C$3,$C$2,C$5),DATE($C$3,$C$2,C$5),HolidayTable)

The results are shown in Figure C. Remember, HolidayTable references the table of holidays you created in the last section (Figure B).

Figure C

Figure C

The NETWORKDAYS() function returns 0s and 1s.

There's already an existing rule that highlights the weekends (if you're working with the demo file or you built your calendar from scratch using the instructions from the 2013 article). You won't always have to accommodate an existing rule, but in this case, you do. The original rule, shown in Figure D, uses the WEEKDAY() function to find the values of 6 and 7 (Saturday and Sunday).

Figure D

Figure D

Work around the existing rule.

At this point, you have a decision to make. You can use the new NETWORKDAYS() function to format Saturdays, Sundays, and holidays by replacing the existing rule — or you can apply the new rule in addition to the existing rule as follows:

  1. Select C4:AG12 — select your calendar.
  2. On the Home tab, click Conditional Formatting in the Styles group.
  3. Choose New Rule.
  4. In the top pane, choose Use a formula to determine which cells to format.
  5. In the formula control, enter the following formula: =C$4=0
  6. Click Format.
  7. Click the Fill tab (if necessary).
  8. Choose a color (I chose red), and click OK (Figure E).
    Figure E
    Figure E
  9. Click OK to see the results (Figure F).
    Figure F
    Figure F

Applying the new rule usurps the first rule, for now. If you're fine with using the same format for both holidays and weekends, you can stop now. If you want two working rules, do the following:

  1. Click Conditional Formatting in the Styles group.
  2. Select Manage Rules.
  3. Choose This Worksheet from the Show formatting rules for drop-down menu.
  4. Find the new rule =C$4=0 and select it.
  5. Press the Move Down arrow (Figure G) to move the rule down. Doing so allows Excel to apply both rules.
    Figure G
    Figure G
  6. Click OK.

As you can see in Figure H, both rules are working separately. The new rule formats only those 0 days where the first format isn't already in place. In this particular case, the position of the rule matters!

Figure H

Figure H

Both rules are working separately.

A final trick

Right now, the =C$4=0 rule highlights all of the 0 values in row 4. Some of those are weekends. You could leave it as is or change the rule as follows:

  1. Click Conditional Formatting in the Styles group.
  2. Select Manage Rules.
  3. Choose This Worksheet from the Show formatting rules for drop-down menu.
  4. Find the new rule =C$4=0 and select it.
  5. The Applies to range for this rule is =$C$4:$AG$12. Click this range (in the Applies to control) and replace the 4 with 5 (Figure I).
    Figure I
    Figure I
  6. Click OK. Figure J shows the result.
    Figure J
    Figure J

You could avoid this last fix by not including row 4 in the selection before you applied the formatting rule. I went this route so you could see how easily you can adapt a rule that's already in place.

At this point, spend some time changing the month and year values to see how the format rules update. You'll find one problem child — there's always one. In 2015, Independence Day falls on a Saturday, and the first rule takes precedence. If you need a rule that formats weekend dates differently when they're also holidays, you'll need a third rule.

Worth noting

It's doubtful that your calendar will be exactly like this one, because you'll most likely have events that require customization. You now have some basic tools that you can modify and enhance to suit your needs. Remember that there's no rule currently in place to alert you when the 29, 30, and 31 aren't in the current month. In addition, if you don't want the 1s and 0s to show in row 4, hide the row or change the font color to white. The other option is to include the full NETWORKDAYS() function in the rule instead of entering it at the sheet level.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox