Calculate business days between two dates

Calculate business days between two dates in Excel

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])

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:

Calculate business days between two dates - 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:

Calculate business days between two dates - total days

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])

Calculate business days between two dates - business days

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:

Calculate business days between two dates - business days minus 1

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!