Tips for Excel, Word, PowerPoint and Other Applications
Calculating Net Work Days in Excel
Why It Matters To You
Simple enough, sometimes you just need to calculate the number of work days in between two calendar dates. You could use simple date math, but this is imprecise as it can miscount weekends and will almost certainly miss holidays. Fortunately for us, there is a function that lets us do exactly this, called NETWORKDAYS.
How To ...
Unfortunately, NETWORKDAYS isn't a function you'll find in the regular set of Excel functions. It comes with the Analysis ToolPak. Luckily, most people can install the Analysis ToolPak with little fuss.
Let's start out by installing the Analysis ToolPak. Go into your Main Menu: Tools: Add-Ins.
You should be presented with a list of available Add-Ins. Put a check mark by Analysis ToolPak and then click OK. Excel may churn for a bit, but before you know it, you'll have the Analysis ToolPak installed.
Now that you have access to the function, let's take a look at how it works. NETWORKDAYS uses 2 mandatory arguments (Start_date and End_date) and a third, optional argument, Holidays. Start_date and End_date are single cell references. Holidays, on the other hand, is a range, representing all of the days you want excluded from the count. As a note, the argument is called Holidays, but the dates can be anything, vacation, out of the office, training, etc.
Open up the test file, networkdays.xls to see how this works. In the example, I want to calculate the number of working days between 1/1/08 and 6/30/08, but excluding all of the dates I have listed as holidays. I'll set up my worksheet to look like this:
The formula looks like this:
NETWORKDAYS(C4,C5,C8:C16)
As you can see, it really is pretty straightforward, it just requires that you think a little about how your data is set up. As a last note, remember, the holidays data can really be any dates you want to exclude, not just holidays.
Notes
| Last updated | 5/22/08 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | Analysis TookPak Add-In |
| Related Tips | Math with Dates |