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 the cases, Saturdays and Sundays are taken as weekends by default.

How to Calculate Working Days in Excel Excluding Weekends & Holidays

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 the Saturdays and Sundays are excluded automatically as weekends. In many countries of the world, the 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 daysWeekend number
Saturday & Sunday1 (default)
Sunday & Monday2
Monday & Tuesday3
Tuesday & Wednesday4
Wednesday & Thursday5
Thursday & Friday6
Friday, Saturday7
Sunday only11
Monday only12
Tuesday only13
Wednesday only14
Thursday only15
Friday only16
Saturday only17

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]

How to Calculate Working Days in Excel Excluding 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.

Related Articles


Siam Hasan Khan on FacebookSiam Hasan Khan on Linkedin
Hello!

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto.

2 Comments
  1. Reply krishna1551989@gmail.com'
    samalakrishna June 25, 2018 at 2:20 PM

    happy, thank you sir

    Leave a reply