How to highlight weekends and holidays in Excel

Weekends and holidays are important to most of us. Besides enjoying the time off, we need to consider them when scheduling projects. Excel’s NETWORKDAYS() and NETWORKDAYS.INTL() functions return the number of days, excluding holidays and weekends, between two dates. Interestingly, you can also use these functions to return 1 and 0, which equate to TRUE and FALSE, respectively. From there, it’s an easy step to applying a conditional format that highlights weekends and holidays in a list of dates.

I’m using Excel 2007 on a Windows 7 system, but all of the ribbon versions support NETWORKDAYS(), the function used in this technique. You can work with your own data or download the .xlsx or .xls demonstration file.

About NETWORKDAYS()

Excel’s NETWORKDAYS() function returns the number of days between two dates, excluding weekends and holidays. This function uses the following syntax:

NETWORKDAYS(start, end, holidays)

where start and end represent the first and last days in the period you’re counting, and holidays is a range that refers to a list of holiday dates.

In Excel 2003, NETWORKDAYS() is available through the Analysis ToolPak add-in, which you can enable as follows:

1. Choose Add-Ins from the Tools menu.
2. Select Analysis ToolPak, and click OK.

The data

The simple data set shown in Figure A has a list of dates and a Table of known holidays–the biggest catch to this technique is that you must specify holidays and reference them as a range. This route gives you control over holidays, and it requires only a bit of extra effort. The example list of holidays is US-based; you’ll want to customize yours to suit your organization’s needs.

Figure A

We’ll highlight the weekends and holidays in this data set.

The holiday list is a Table, so you can automatically update it without updating the NETWORKDAYS() function that we’ll add in a moment. The list of dates in column B isn’t a Table, but it easily could be. In this case, it won’t make any difference to the function we’re about to explore. If you’re using Excel 2003, you can’t convert your list of holidays into a Table, so you’ll have to use a dynamic range or update your NETWORKDAYS() function if you add new holiday dates.

If you don’t know how to create a Table, here’s how:

1. Click inside the data set.
2. Click the Insert tab.
3. Click Table inside the Tables group.
4. In the resulting dialog, check (or not) the My table has headers option (Figure B), and click OK.
   Figure B
   

Now, let’s continue by entering the NETWORKDAYS() function in C4. If you’re using a Table object for your holidays, use the following function:

=NETWORKDAYS(B4,B4,Table1[Date])

If you’re using Excel 2003, enter the holiday range:

=NETWORKDAYS(B4,B4,$F$4:$F$12)

After entering the function, copy it to the remaining dates, as shown in Figure C. As you can see, this function returns 0 for dates that fall on Saturday and Sunday and 1 for dates that fall on Monday through Friday. You might also notice that two weekday dates, Jan 1, 2016, and January 18, 2016, are weekdays, but the function returns 0. Those dates are listed in the holiday list in column F. Since this function’s purpose is to count workdays, it also excludes the holidays.

Figure C

Copy NETWORKDAYS().

Remember, if you add dates to the holiday list in column F, you must update the holiday argument to reflect the new row(s).

The conditional format

Now, it’s time to apply the conditional format rule that will highlight all weekend and holiday dates in your date list in column B:

1. Select B4:B49 (you want to select the list of dates you want to apply the conditional format to).
2. In the Styles group (on the Home tab), click Conditional Formatting and, from the drop-down list, click New Rule.
3. In the resulting dialog, click the Use a formula to determine which cells to format option in the upper pane.
4. Enter the expression =$C4=0 and click Format.
5. Click the Fill tab, choose a color, and click OK. At this point, you can see the rule and the format (Figure D).
   Figure D
   
6. Click OK to return to the worksheet.

Figure E shows the conditional format at work. All Saturdays, Sundays, and holidays (as defined in column F) are highlighted in red (or the color you chose in step 5).

Figure E

Highlight weekends and holidays.

You can add the function to the conditional format rule (step 4) instead of adding the function to the sheet (adding it to the sheet allows you to see how the function works). If you want to omit column C, use the following expression in step 4:

=NETWORKDAYS(B4,B4,$F$4:$F$12)=0

Custom weekends

Excel 2007’s NETWORKDAYS() function defines a weekend date as a Saturday or Sunday. That won’t always work for every organization. Excel 2010 and later offer a second function that lets you specify which days are weekend days. NETWORKDAYS.INTL() uses the following syntax:

NETWORKSDAYS.INTL(start, end, [weekend], [holidays])

where weekend is one of the integers in Table A. For example, if your weekend consists of Sunday and Monday, you’d use the argument value of 2.

Table A

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. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at susansalesharkins@gmail.com.