How to calculate working days in Excel excluding weekends & holidays

It is often required in Excel to find out the total number of working days between two dates. Usually while counting this, we often neglect the weekends and holidays. Excel has two different functions to exclude the weekends and holidays while counting the number of workdays. In this article, we will see how to calculate working days in Excel excluding weekends and holidays.

Count Workdays excluding Holidays and Weekends (Saturday & Sunday)

The function NETWORKDAYS is used while counting the total number of workdays in Excel. While counting the workdays using this function Saturdays and Sundays are considered as weekends by default. The syntax of this function is, =NETWORKDAYS (start_date, end_date, [holidays]). Here, the argument holiday is an optional argument. It is used when you want to calculate the working days excluding the holidays.

Look into the below example where I calculated the number of working days for the month of June in two different scenarios. One scenario is including the holidays and the other one is excluding the holidays. In both cases, Saturdays and Sundays are taken as weekends by default.

As you can see, the first formula counts the total number of working days where the holidays are not excluded. From cell B7 to cell B11, the holidays are given and the second formula counts the number of working days excluding the holidays.

The formula used in here are =NETWORKDAYS(B2,C2) and =NETWORKDAYS(B4,C4,B7:B11).

Count Workdays excluding Holidays and Specific Weekends

In the previous example, you saw the process of counting the working days with and without the holidays where Saturdays and Sundays are excluded automatically as weekends. In many countries of the world, weekends are different. Some countries consider Friday and Saturday as weekends. In this case, the previous formula will not work. Excel has another built-in function like the NETWORKDAYS where you can insert the weekends with your own choice. This function is, NETWORKDAYS.INTL. The syntax of this function is, =NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays]). Here, the arguments weekend and holidays are the optional arguments. If you don’t insert any number in the weekend argument, the formula will consider Saturday and Sunday as weekends.

Before counting the total number of working days with the function NETWORKDAYS.INTL, look at the table below. The numbers in this table are used in the weekend argument section of the NETWORKDAYS.INTL function.

As you can see, each number indicates different weekend days.

In this example, I will be conducting the same calculation as I used earlier. Here I will calculate the number of working days with 3 different scenarios. The 3 scenarios are, counting workdays excluding Saturdays and Sundays as weekends, counting workdays excluding specific weekends and counting workdays excluding holidays and specific weekends.

The formulas we will be using here are

• =NETWORKDAYS.INTL(B2,C2) [Count workdays excluding Saturdays and Sundays as weekends]
• =NETWORKDAYS.INTL(B4,C4,7) [Count workdays excluding specific weekends]
• =NETWORKDAYS.INTL(B6,C6,7,B10:B13) [Count workdays excluding holidays and specific weekends]

As you can see, three different scenarios have been discussed. The last scenario is for counting the number of working days excluding specific weekends and holidays.

Conclusion

This article is about counting the number of working days in Excel excluding the weekends and holidays. As you can see, there are two functions which are used to calculate the working days. The procedure is very simple and doesn’t require too many options.

Hope you will find this article useful. Stay connected for more interesting and useful articles.

Further Readings

• Add days to date in Excel excluding weekends and holidays (4 ways)
• Excel Formula to Count Days from Date to Today (8 Effective Ways)
• Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)
• How to Calculate Average Tenure of Employees in Excel
• Number of Days Between Two Dates Calculator
• Excel Formula to Count Days from Date
• How to Find Number of Months between Two Dates
• How to calculate years of service in Excel (3 easy ways)
• Calculate The Difference between Two Dates in Excel