Formula for Business Days in Excel (3 Examples)

Not all 7 days of a week are business days! Business days indicate the working days. In an official week, weekends exist too. Not only that, there are some government Holidays too. In this article, we will see how to calculate the business days in Excel using formula.


Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


3 Examples of Formula for Business Days in Excel

In this article, we will explain some formulas to find out the business days in Excel. In the Excel sheet, we gathered all the information necessary to calculate business days between dates.


1. Use NETWORKDAYS Function to Calculate Business Days in Excel

The NETWORKDAYS function returns the number of whole workdays between two dates.

In this article, we will use the NETWORKDAYS function to find the business days.

📌 Steps:

  • First, we add two new cells to calculate the total days and business days.

  • Go to Cell F6. Put the following formula to get the total days between the dates.
=F5-F4

Use NETWORKDAYS Function to Calculate Business Days in Excel

  • Press the Enter button to get the result.

  • Now, we will find out the business days between the days. Put the formula on Cell F7.
=NETWORKDAYS(F4,F5)

Use NETWORKDAYS Function to Calculate Business Days in Excel

  • Press the Enter button and get the result.

But the thing is, the govt. holidays are included in the workdays too. We need to deduct the holidays from the result.

  • Again, go to Cell F7 and modify the formula. Hence the formula becomes:
=NETWORKDAYS(F4,F5,$C$6:$C$14)

Use NETWORKDAYS Function to Calculate Business Days in Excel

  • Finally, press the Enter button to get the result.

This result is based on 2 weekends with govt. holidays.

Read More: Last Business Day of the Month in Excel (9 Examples)


2. Apply NETWORKDAYS.INTL Formula to Get Business Days in Excel

The NETWORKDAYS.INTL function returns the number of whole weekdays between two dates with custom weekend parameters.

Sometimes we see that some organizations do not follow the conventional weekends of Saturday and Sunday. They may have different weekends or 1 day as the weekend. For those cases, we can use this NETWORKDAYS.INTL function. This function has the facility to customize the weekends.

📌 Steps:

  • We already know the total days’ difference between the days.

NETWORKDAYS.INTL Formula to get business days

  • Now, go to Cell F7 and put the formula below.
=NETWORKDAYS.INTL(F4,F5,11)

NETWORKDAYS.INTL Formula to get business days

Here, we choose 11 in the last argument. That indicates Sunday as the weekend. We can also choose other days as weekends from the list.

  • Press the Enter button.

Here is the result. But we did not deduct the holidays here.

  • Again, go to Cell F7 and modify the formula.
=NETWORKDAYS.INTL(F4,F5,11,$C$6:$C$14)

NETWORKDAYS.INTL Formula to get business days

We put the range of holidays in the 4th argument.

  • Finally, press the Enter button.

We get the perfect result.


Similar Readings


3. Combine SUM, INT and WEEKDAY Functions to Get Business Days

The SUM function adds all the numbers in a range of cells.

The INT function rounds a number down to the nearest integer.

The WEEKDAY function returns a number from 1 to 7 identifying the day of the week of a date.

Here, we will use a combination of the SUM, INT, and WEEKDAY functions. But there is an issue with this formula, we can not deduct the holidays. It will just remove the custom weekends.

📌 Steps:

  • Go to Cell F7 and put the formula below.
=SUM(INT((WEEKDAY(F4-{2,3,4,5,6})+F5-F4)/7))

Combine SUM & WEEKDAY Functions to Get Business Days in Excel

  • Finally, press the Enter button.

Here, we get the result based on customized weekends.

Formula Explanation:

  • WEEKDAY(F4-{2,3,4,5,6})

In this section, we used the WEEKDAY function. These count days from the week of the 2nd to the 6th.

Result: [5,4,3,2,1]

  • INT((WEEKDAY(F4-{2,3,4,5,6})+F5-F4)/7)

Now, we apply the INT function. We will divide the result by 7 and get the nearest integer number.

Result: [26,26,26,26,25]

  • SUM(INT((WEEKDAY(F4-{2,3,4,5,6})+F5-F4)/7))

Finally, apply the SUM function. It will add all the working days.

Result: 129

Read More: How to Convert Date to Day of Week in Excel (8 Methods)


Things to Remember

When we need only the business days ducting the weekend we can use any of the formulas. But in case of deducting the holidays the combination of the SUM, INT and WEEKDAY are not usable.


Conclusion

In this article, we showed 3 formula to get the business days in Excel. We also explain all the formulas in detail. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo