How to Calculate Business Days in Excel (3 Easy Formulas)

Where are you from? Are you a TGIF person or is your Saturday spirit drowning somewhere behind the office walls? The most common understanding of a weekend is Saturday and Sunday. The point to note here is that it’s common but not always the case.

In some parts of the world, weekends may comprise any other two continuous days of the week (such as Friday and Saturday in some eastern countries), discontinuous days, or even a single day.

If your task is to find the business days or working days in a certain period, this may be required to ascertain deadlines, timelines or to further calculate expenditure, income, or some other entitlement. How can we get Excel to understand what our weekend days are?

Today’s tutorial will answer this for you. You can use a combination of the WEEKDAY, INT, and SUM functions or you can go with the NETWORKDAYS or NETWORKDAYS.INTL function. Allow us to demonstrate how to use each one according to your weekend days and holidays. We’ll head to the example to work on.

How to Calculate Business Days in Excel

Example

Let’s cast a quick look at the example:

How to Calculate Business Days in Excel

In our example case today, we’re looking at a semester schedule for a school and are required to calculate the total working days for the staff in each semester. A complete semester is measured by the days between the first day of the semester (start date) to the last day (end date).

Getting the total days between the two dates is no problem at all; even the arithmetic difference will get you what you’re looking for:

How to Calculate Business Days in Excel

Deducting the end date from the start date gave us the total days. If you’re working 7 days of the week, the difference between the two dates gives you the working days. Now how to get Excel to exclude weekends? It’s not complicated at all and luckily enough for you today, Excel has perfectly-suited functions for the purpose.

Let’s get down to business!

Using SUM & WEEKDAY Functions

Before we move on to specific functions and how to branch out with them to include custom parameters, let's run through another trick first. Using the SUM and WEEKDAY functions together can calculate the working days between two dates. The formula below is what you need to use to arrive at the working days with the SUM and WEEKDAY functions:

=SUM(INT((WEEKDAY(D3-{2,3,4,5,6})+E3-D3)/7))

For now, we’re assuming to be dealing with a regular Saturday-Sunday weekend. In the formula, 2,3,4,5,6 signifies Saturday and Sunday as the weekend days. The mentioned numbers will be considered as a working day, 1 being Sunday. Read the note below on how to customize these numbers for custom weekends.

E3-D3 gives the total days between the two dates. Added to WEEKDAY’s result and divided by 7 lands the result as a decimal. Due to the SUM and INT functions, only the integer part will be added as the decimal will be rounded down to the nearest integer.

This finally gives us the total working days between the start and end date. Below is the formula applied to our case example:

Using SUM & WEEKDAY Functions

Note: You also have the option of changing the numbers in this formula to create a custom weekend. The number 1 represents Sunday. Leave out the numbers that represent your weekend. This formula will also work for non-consecutive weekends like Friday and Sunday making the numbering 2,3,4,5,7.

Pretty cool right? Cooler enough, there are less complex options where we set weekends and even include holidays. Keep scrolling!

Using NETWORKDAYS Function

The NETWORKDAY function is tailor-made for calculating business days or working days. The function assumes a Saturday-and-Sunday weekend by default and returns the working days between two dates by excluding Saturdays and Sundays.

Other than weekends, you also have the option of listing the holidays on your worksheet and getting the NETWORKDAYS to exclude those too. Let’s see how you can use the NETWORKDAYS function with and without holidays.

Working Days between Two Dates (Excluding Holidays)

First, we’ll tap the penny-plain version of the NETWORKDAYS function, simply using it to calculate the working between two dates. Use the formula below:

=NETWORKDAYS(D3,E3)

The NETWORKDAYS function has been supplied with only two arguments; the start date and the end date. All the function has to do, is compute the days between the two dates, excluding Saturdays and Sundays from the count. This is how it has worked for our example:

Working Days between Two Dates (Excluding Holidays)

Working Days between Two Dates (Including Holidays)

Now let’s add a component and see how that affects the results. Not only does the NETWORKDAYS function keep weekends out of the count, but it also excludes a given list of holidays. That’s peanuts for NETWORKDAYS as keeping holidays out of the calculation for working days is the job of the third and last argument of the function. You’ll have to apply the following formula:

=NETWORKDAYS(D3,E3,$D$11:$D$22)

Between the dates in D3 and E3 the computation of the working days needs to consider the holiday dates in D11:D22. The holidays have been entered with $ signs which make it an absolute reference in the formula to keep the reference locked.

These holidays need to be listed in the worksheet or anywhere in the workbook and referred appropriately. Now see how the working days are one day lesser with a holiday falling in that term:

Working Days between Two Dates (Including Holidays)

Using NETWORKDAYS.INTL Function

What’s the inherent problem with the NETWORKDAYS function? It assumes Saturday and Sunday to be the weekend. It would be pointless to use NETWORKDAYS where there’s a one-day weekend or where the weekend falls on other days. For custom-setting the weekend, we can turn to the NETWORKDAYS.INTL function.

This function can do everything that the NETWORKDAYS function can do with the added component of a custom weekend. Let’s start so you can explore the options.

Working Days between Two Dates (With Custom Weekends)

When calculating business or working days, the focal point is the weekend which is not the same everywhere in the world. Your weekend may be made up by some other two days or a single day in the week. No matter, you can make NETWORKDAYS.INTL accept your weekend. See how we go with one such option with this formula:

=NETWORKDAYS.INTL(D3,E3,11)

To measure the working days between D3 an E3, the NETWORKDAYS.INTL function will take a third argument aided by Formula AutoComplete for selecting the weekend day(s). Without this argument, the function will assume a Saturday-Sunday weekend. We will select the code 11 for only Sunday as the weekend:

Working Days between Two Dates (With Custom Weekends)

With Sunday as the chosen weekend, our case example looks like this:

Working Days between Two Dates (With Custom Weekends)

Working Days between Two Dates (With Non-Consecutive Custom Weekends)

You will have to consider this method if you are calculating the working days for a part-time job or if your two-day weekend doesn’t fall on consecutive days. If so, the third argument in the NETWORKDAYS.INTL function doesn't have an option for non-consecutive weekend days listed in the Formula AutoComplete. But there’s a workaround; we can formulate a code as an input in the function which will define the weekend days.

Check out the formula below for making Friday and Sunday (like in some parts of Malaysia and Indonesia) the weekend days in the NETWORKDAYS.INTL function:

=NETWORKDAYS.INTL(D3,E3,"0000101")

This time we have fed the NETWORKDAYS.INTL function with a code for considering the weekend days as Friday and Sunday. This requires 7 digits, each digit representing the day of the week, starting with Monday. According to that, the last and third-last digit makes Sunday and Friday respectively.

The code key according to the weekday initials is:

MTWTFSS

This code needs to be made up of 0’s and 1’s where:

  • 0 is a working day
  • 1 is a non-working day.

Consequently, our code looks like this "0000101". Applied to our example to find the working days between the start date and end date of the semesters, the formula with the NETWORKDAYS.INTL function looks like this:

Working Days between Two Dates (With Non-Consecutive Custom Weekends)

Working Days between Two Dates (With Custom Weekends & Holidays)

And the final addition to the formula is the inclusion of holidays, like the NETWORKDAYS function. The NETWORKDAYS.INTL can incorporate custom weekends and holidays in the formula to arrive at the working days between two dates. You’re already familiar with the concept from earlier so let’s dive right into the formula.

=NETWORKDAYS.INTL(D3,E3,11,$D$11:$D$22)

Plucking the previously used formula with the custom weekend as Sunday, the final argument takes holidays for counting the working days between D3 and E3. The reference of the holidays listed on the worksheet has been supplied in the formula as $D$11:$D$22.

Now the NETWORKDAYS.INTL function will leave only Sundays and the mentioned holidays out of the count and return the working days between D3 and E3:

Working Days between Two Dates (With Custom Weekends & Holidays)

You must have the working days' calculation drilled in your mind by now. Which should be quite useful; working in Excel, you may find yourself often tangled up in such number work. Happy untangling for now! We’ll just be around, readying ourselves for untangling more Excel mess. Excel comb, here we go!

About Ankit Kaul

Ankit is a die-hard fan of Microsoft Excel and has been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'.