In project management, it’s useful to be able to find out how many working days are between two dates. You may have a project start date and end date, and you’d like to know how many working days there are within those two dates. The big reason to get this info is probably because project managers want to get a sense of how many days it takes to get work done. This is helpful to allow them to better estimate future projects. So how do we calculate the business days between two dates, anyway? And what about holidays? How can we account for them as well?
NETWORKDAYS() Excel Function
There is a function in Excel called
NETWORKDAYS() which can calculate the amount of business days between two dates. You can also tell this function which holidays you’d like to exclude.
Here is the syntax:
NETWORKDAYS(start_date, end_date [, holidays])
||The beginning date to use in the function.|
||The end date to use in the function.|
||Optional. This is either a range that contains the dates of holidays or an array constant of serial numbers.|
Let’s say we had the following data:
The Excel Table on the left is called “ProjectDates” and the Excel Table on the right is called “Holidays.” We’re also going to make use of Structured References for these formulas to make the formulas simpler.
Let’s start by adding the total days. To get this, we can do the simple formula (in column
=[@[End Date]]-[@[Start Date]]
Then format the data like a number, like so:
Pretty straightforward. The first project has 29 days total and the second project has 7 days.
Also, if you’re wondering why the formula produced dates of
1/7/1900, click here.
Now let’s get the business days with the
NETWORKDAYS() function. We’re going to include the Holidays list as well to exclude holidays.
Here is the formula we’ll use in column
=NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays[Date])
Wait, wait….why does row 3 say the number of business days is 6 instead of 5?
The Excel function
NETWORKDAYS() uses the start date and end date inclusively when calculating the total business days. So for row 3 the end date of
3/8/2017 counts as a business day as well.
If you’d like to adjust this, simply subtract 1 from the total: