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.
Weekend days | Weekend number |
Saturday & Sunday | 1 (default) |
Sunday & Monday | 2 |
Monday & Tuesday | 3 |
Tuesday & Wednesday | 4 |
Wednesday & Thursday | 5 |
Thursday & Friday | 6 |
Friday, Saturday | 7 |
Sunday only | 11 |
Monday only | 12 |
Tuesday only | 13 |
Wednesday only | 14 |
Thursday only | 15 |
Friday only | 16 |
Saturday only | 17 |
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
happy, thank you sir
You are welcome.
This is very good. Loved the article.
Glad to know that you liked this article.
Thanks for the feedback.
can the NETWORKDAYS (or a similar function) be used without having to manually enter all of the weekend and holiday dates?
Hi, Kawser
Thanks you so much, you are making people life easy and Inshallah ALLAH will pay you back.
Have a wonderful time
Thanks
M. Shakeel
Thanks for your Prayer, Shakeel!
Best regards
Kawser