How to Calculate Working Days in Excel Excluding Weekends and Holidays

Consider a dataset with start and end dates for employees, where we need to calculate how many working days they had. There are a few ways to do so.


Method 1 – Using NETWORKDAYS Function to Calculate Working Days in Excel Excluding Weekends and Holidays

The NETWORKDAYS function calculates the number of workdays between two dates considering both weekends and holidays. This function assumes that the weekend is on Saturday and Sunday.


Case 1.1 Excluding Only Weekends

Steps:

  • Select the E5 cell and write down the following formula:
=NETWORKDAYS(B5,C5)
  • Hit Enter.

inserting networkdays function to calculate working days in excel excluding weekends and holidays

  • We will get the net workdays excluding the weekends.
  • Pull the cursor down to the last data cell to get the values for all the data.


Case 1.2 Excluding Both Weekends and Holidays

Let’s expand the dataset with an array of holiday dates between D13:D15.

Steps:

  • Select the E5 cell and then write the following formula down:
=NETWORKDAYS(B5,C5,$D$13:$D$15)

In this case, the $D$13:$D$15 static range denotes the holidays.

  • Hit Enter.

typing networkdays function to calculate working days in excel excluding weekends and holidays

  • We will get the net workdays excluding the weekends as well as the holidays.
  • Click on the bottom-right edge of the cell and drag the cursor to the last data cell.
  • Excel will automatically fill the rest of the cells according to the formula.

considering both weekends and holidays in networkdays function to calculate working days in excel excluding weekends and holidays

Read More: How to Calculate Working Days between Two Dates in Excel


Method 2 – Applying Excel NETWORKDAYS.INTL Function to Calculate Working Days Excluding Weekends and Holidays


Case 2.1 Excluding Only Weekends

Steps:

  • Choose the E5 cell and copy the following formula:
=NETWORKDAYS.INTL(B5,C5,7)
  • Hit Enter.

writing networkdays.intl function to calculate working days in excel excluding weekends and holidays

  • We will receive the net workdays excluding weekends.
  • Copy the formula to the other cells in the column.

In this case, the third argument is 7 which denotes a Friday and Saturday weekend. The following is the list of numbers that denote different weekends. You might want to use 1 as well.


Case 2.2 Excluding Both Weekends and Holidays

Let’s use the expanded dataset with an array of holiday dates between D13:D15.

Steps:

  • Choose the E5 cell and write down the following formula,
=NETWORKDAYS.INTL(B5,C5,7,$D$13:$D$15)
  • Hit the Enter button.

typing networkdays.intl function to calculate working days in excel excluding weekends and holidays

  • We will get the total workdays excluding the weekends and holidays.
  • Copy the formula by clicking on the bottom-right corner of E5 and dragging down.
  • The rest of the cells will be automatically filled according to the formula.

Read More: How to Calculate Working Days Excluding Sundays in Excel


Download Practice Workbook


Related Articles


<< Go Back to Excel WORKDAY Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Siam Hasan Khan
Siam Hasan Khan

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 an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

9 Comments
  1. happy, thank you sir

  2. This is very good. Loved the article.

  3. can the NETWORKDAYS (or a similar function) be used without having to manually enter all of the weekend and holiday dates?

  4. 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

  5. How do you find out weeks?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jun 13, 2024 at 11:35 AM

      Hello Robert

      Thanks for your question! You want to calculate the number of weeks between two dates. To do so, you can apply several formulas:

      1. Using NETWORKDAYS: =INT(NETWORKDAYS(B6,C6)/5)
      2. Using DATEDIF: =INT(DATEDIF(B6, C6, "d")/7)
      3. Using Arithmetic: =INT((C6-B6)/7)
      4. Using WEEKNUM: =WEEKNUM(C6)-WEEKNUM(B6)

      Hopefully, you have found the solutions you were looking for. I have attached the solution workbook as well. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo