Calculate business days between two dates in Excel
September 05, 2017
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?
Does this article help you? If so, please consider supporting me with a coffee ☕️
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])| Parameter | Description |
|---|---|
start_date |
The beginning date to use in the function. |
end_date |
The end date to use in the function. |
holidays |
Optional. This is either a range that contains the dates of holidays or an array constant of serial numbers. |
NETWORKDAYS() Example
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 C) of:
=[@[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/29/1900 and
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 D:
=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:
