Alok Paul
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
- 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)
- 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)
- 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.
- Now, go to Cell F7 and put the formula below.
=NETWORKDAYS.INTL(F4,F5,11)
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)
We put the range of holidays in the 4th argument.
- Finally, press the Enter button.
We get the perfect result.
Similar Readings
- How to Convert Text to Date with Excel VBA (5 Ways)
- Get the First Day of the Current Month in Excel (3 Methods)
- How to Convert Date to Year in Excel (3 Quick Ways)
- Convert Date to Day of Year in Excel (4 Methods)
- How to Convert Date to Day in Excel (7 Quick Ways)
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))
- 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
- VBA to Remove Time from Date in Excel (3 Methods)
- How to Change Date Format in Pivot Table in Excel
- Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
- How to Use Formula to Change Date Format in Excel (5 Methods)
- Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- How to Change Default Date Format from US to UK in Excel (3 Ways)
