How to Calculate Working Days between Two Dates in Excel (4 Methods)

In this tutorial, we will explain how to use different functions to calculate working days between two dates in excel. Sometimes, it becomes very essential to calculate working days between two dates in project management. Throughout this article, we will use multiple functions with unique datasets to illustrate the process of calculating working days between two dates in excel.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Methods to Calculate Working Days between Two Dates in Excel

Microsoft Excel provides us with a lot of functions to perform different tasks. In the 4 methods of this article, we will use the NETWORKDAYS and the NETWORKDAYS.INTL functions that are dedicated to calculating working days between two dates. We will also use a combination of the SUM, INT & WEEKDAY functions to get the same result.


1. Excel NETWORKDAYS Function to Calculate Working Days between Two Dates

Excel’s NETWORKDAYS function calculates the number of workdays between two dates. We can also exclude holidays from our working days which is an optional argument of this function.

1.1 Calculate Working Days between Two Dates in Excel Excluding Holidays

In this method, we will use the NETWORKDAYS function to calculate working days between two dates. We will not consider the holidays in this method. So, this process will only consider Saturday and Sunday as weekends. In the following dataset, we have the Start Date and End Date for different works. We will calculate the total working days in that period in the column named Working Days.

Calculate Working Days between Two Dates in Excel Excluding Holidays

So, let’s see the steps to perform this action.

STEPS:

  • Firstly, select cell E5. Insert the following formula in that cell:
=NETWORKDAYS(B5,C5)

Calculate Working Days between Two Dates in Excel Excluding Holidays

  • Now, press Enter.
  • The above action returns the value of working days between the dates “1-01-22” and “31-12-22” in cell E5. We can see from the following image that the value of working days for that range is 260.

  • Secondly, select cell E5. Move the mouse cursor to the bottom right corner of the selected cell so that it turns into a plus (+) sign like the following image.

Calculate Working Days between Two Dates in Excel Excluding Holidays

  • Thirdly, click on the plus (+) sign and drag the Fill Handle down to cell E10 to copy the formula of cell E5 in other cells. We can also double-click on the plus (+) sign to get the same result.

  • After that, release the mouse click.
  • Lastly, we can see the values of working days excluding holidays in cells (E5:E10).


1.2. Include Holidays While Calculating Working Days between Two Dates

Unlike the previous example, we will consider holidays in this example to calculate working days between two dates with the NETWORKDAYS function. To illustrate this method we will use the same dataset but this time we have an additional list of holidays. We can see the holidays in the following image of the dataset.

Include Holidays While Calculating Working Days between Two Dates

Let’s take a look at the steps to perform this action.

STEPS:

  • First, select cell E5. Input the following formula in that cell:
=NETWORKDAYS(B6,C6,$D$13:$D$15)

Include Holidays While Calculating Working Days between Two Dates

  • Now press Enter.
  • The above command returns the number of working days between the dates “1-01-22” and “31-12-22”. The formula used in this method considers the value of range (D13:D15) as a holiday. The value of working days for that range is 257, as seen in the following image.

Include Holidays While Calculating Working Days between Two Dates

  • Next, select cell E5. Drop the mouse pointer to the selected cell’s bottom right corner, where it will convert into a plus (+) sign, as shown in the image below.
  • After that, to copy the formula of cell E5 in other cells click on the plus (+) sign and drag the Fill Handle down to cell E10. We can also double-click on the plus (+) sign to get the same result.

  • Now, free the mouse click.
  • Finally, we get all the values of working days in cells (E5:E10).

Read More: Calculate Number of Days Between Two Dates in Excel (8 Quick Tricks)


2. Apply NETWORKDAYS.INTL Function to Calculate Working Days with Custom Holidays

The NETWORKDAYS.INTL function is similar to the NETWORKDAYS function. Both of the functions calculate working days between two dates. But the NETWORKDAYS.INTL function allows us to select which day we will consider as a holiday. The NETWORKDAYS.INTL function considers only Sunday as an international holiday. So, it will consider Saturday as a working day. To illustrate this method we will perform the previous method again with the NETWORKDAYS.INTL function.

Apply NETWORKDAYS.INTL Function to Calculate Working Days with Custom Holidays

So, just go through the following steps to perform this action.

STEPS:

  • In the beginning, select cell E5. Write down the following formula in that cell:
=NETWORKDAYS.INTL(B5,C5,11,$D$13:$D$15)

Apply NETWORKDAYS.INTL Function to Calculate Working Days with Custom Holidays

  • Now, press Enter.
  • The number of working days between the dates “1-01-22” and “31-12-22” is returned by the above command. This method’s calculation interprets the value of the range (D13:D15) as a holiday. we can see the number of working days in that range is 310. The value is larger than the previous results as it is not taking Saturday as a holiday.

Apply NETWORKDAYS.INTL Function to Calculate Working Days with Custom Holidays

  • Next, click on cell E5. Slide the mouse cursor to the bottom right corner of the selected cell, where it will turn into a plus (+) sign, as shown below.
  • Then, click the plus (+) sign and drag the Fill Handle down to cell E10 to duplicate the formula from cell E5. To get the same outcome, we can double-click on the plus (+) sign.

  • After that, drop the mouse click now.
  • Finally, we have all of the working day’s values in cells (E5:E10).

Related Content: How to Calculate Remaining Days in Excel (5 Methods)


Similar Readings:


3. Calculate Number of Working Days in a Part-Time Job in Excel

The number of working days for a part-time job is not the same as the regular one. We have to add some extra modifications to the function NETWORKDAYS.INTL to extract the working days in a part-time job. To illustrate this method we will use the same dataset that we used in the first example.

Calculate Number of Working Days in a Part-Time Job in Excel

Let’s take a look at the steps to perform this method.

STEPS:

  • Firstly, select cell E5. Insert the following formula in that cell:
=NETWORKDAYS.INTL(B5,C5,"1010111")

Calculate Number of Working Days in a Part-Time Job in Excel

  • Now, hit Enter.
  • The above command returns the number of part-time working dates between the dates “1-01-22” and “31-12-22“. We can see that the value of the working day between this range is 104.

Calculate Number of Working Days in a Part-Time Job in Excel

  • Secondly, go to cell E5. Put the mouse cursor to the bottom right corner of the selected cell, where it will convert into a plus (+) sign, as shown in the figure below.
  • Thirdly, tap on the plus (+) sign and drag the Fill Handle down to cell E10 to paste the formula from cell E5. We can double-click on the plus (+) sign also to get the same result.

  • After that, free the mouse click.
  • Lastly, we have all of the working day’s values in cells (E5:E10).

NOTE:

In this method, we have used “1010111” in lieu of selecting the weekend from the function’s built-in option.

  • 0 represents a working day.
  • 1 represents a non-working day.

Here the first number of the sequence indicates Monday whereas the last number indicates Friday. So, the sequence “1010111” means that only Tuesday and Thursday are working days, and the rest of the days of the week are non-working days.

Related Content: Excel Formula to Find Date or Days in Next Month (6 Quick Ways)


4. Combine Excel SUM and INT Functions to Calculate Working Days Between Two Dates

Apart from the NETWORKDAYS and the NETWORKDAYS.INTL function, there’s also another method that can be used to get the number of workdays between two dates. In this method, we will use a combination of the SUM and INT functions with the assistance of the WEEKDAY function to calculate working dates between two dates. In excel the SUM function is used to add multiple numbers together. The INT function converts a number to its closest integer value. The WEEKDAY function returns a weekday that matches a particular date. We’ll use the same dataset as in the first example to demonstrate this procedure.

Combine Excel SUM and INT Functions to Calculate Working Days Between Two Dates

Now, just go through the steps to perform this method.

STEPS:

  • First,select cell E5. Write down the following formula in that cell:
=SUM(INT((WEEKDAY(B5-{2,3,4,5,6})+C5-B5)/7))

Combine Excel SUM and INT Functions to Calculate Working Days Between Two Dates

  • Now, hit Enter.
  • We get the value of working days between the dates “1-01-22” and “31-12-22” in cell E5 by the above command. We get the value 260 in cell E5.

Combine Excel SUM and INT Functions to Calculate Working Days Between Two Dates

  • Next, select cell E5. Move the mouse pointer to the bottom right corner of the selected cell, where it will switch into a plus (+) sign, as displayed below.
  • Then, simply tap the plus (+) sign and drag the Fill Handle down to cell E10 to paste the formula from cell E5. To get the same outcome, we can double-click on the plus (+) sign also.

  • After that, release the mouse click.
  • Finally, we get all the values of working days in cells (E5:E10).

🔎 How Does the Formula Work?

  • WEEKDAY(B5-{2,3,4,5,6}): The values 2,3,4,5 & 6 indicate five working days in a week starting from the date in cell B5.
  • INT((WEEKDAY(B5-{2,3,4,5,6})+C5-B5)/7): This part returns a number of working days per week.
  • SUM(INT((WEEKDAY(B5-{2,3,4,5,6})+C5-B5)/7)): Returns the total working days from date “1-01-22” to “31-12-22”.

Related Content: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)


Conclusion

In conclusion, this post will show you how to calculate working days between two dates in Excel. To put your skills to the test, use the practice worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more unique Microsoft Excel solutions.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo