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

Method 1 – Use the Excel NETWORKDAYS Function to Calculate Working Days Between Two Dates

Case 1.1 – Calculate Working Days Between Two Dates Excluding Only Weekends

This case will only consider Saturday and Sunday as non-working days. We have the Start Date and End Date for different projects, and we will calculate the total working days in those periods in the column named Working Days.

Steps:

• Select cell E5. Insert the following formula in it:

• Press Enter.

• Select cell E5 and move the mouse cursor to the bottom-right corner so that it turns into a plus (+) sign, which is the Fill Handle tool.

• Click and drag the Fill Handle down to cell E10 to copy the formula to the other cells. You can also double-click on the plus (+) sign to get the same result.

• You can see the values of working days in cells (E5:E10).

Case 1.2 – Exclude Holidays While Calculating Working Days between Two Dates

We will use the same dataset, but this time we have an additional list of holidays in a smaller table below, starting at D13.

Steps:

• Select cell E5.
• Input the following formula in that cell:

• Press Enter.

• Select cell E5 and hover over the bottom-right corner to get the Fill Handle icon.
• Drag the Fill Handle down to cell E10, or double-click on the plus (+) sign to get the same result.

• You’ll get all the values of working days in cells (E5:E10).

Read More: How to Find Last Business Day of Month in Excel

Method 2 – Apply the NETWORKDAYS.INTL Function to Calculate Working Days with Custom Weekends

The NETWORKDAYS.INTL function can use various arguments to select which weekend days you want to use for calculations, and also accepts custom holidays.

Steps:

• Select cell E5.
• Enter the following formula in that cell:

• Press Enter.
• In the function above, the third argument “11” is chosen to count only the Sunday as a weekend day, so the result will be higher than with the previous methods. You can go to the NETWORKDAYS.INTL article to learn more about it.

• Click on cell E5 and hover over the bottom-right corner to get the Fill Handle icon (plus sign).
• Click and drag the Fill Handle down to cell E10 to duplicate the formula or double-click on the icon to fill in throughout the column.

• We have all of the working day’s values in cells (E5:E10).

Method 3 – Calculate the Number of Working Days in a Part-Time Job in Excel

You can use a custom string argument in the NETWORKDAYS.INTL function to extract the working days in a part-time job. Let’s say that the only working days are Tuesday and Thursday.

Steps:

• Select cell E5.
• Insert the following formula in that cell:

• Hit Enter.

• Go to the result cell and hover over the bottom-right corner to get the Fill Handle.
• Double-click on the Fill-Handle to AutoFill.

• Here are the results for the sample.

NOTE:

In this method, we have used the string “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.

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. Note that you have to input a string value, so use quotes.

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

We’ll use the same dataset as in the first example for demonstration.

Steps:

• Select cell E5.
• Put the following formula in that cell:

• Hit Enter.

• Select cell E5.
• Move the mouse pointer to the bottom right corner of the selected cell, where it will switch into a plus (+) sign.
• Click the plus (+) sign and drag the Fill Handle down to cell E10 to paste the formula from cell E5.

• 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”.

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

Download the Practice Workbook

Related Articles

• How to Calculate Working Days in Excel Excluding Weekends and Holidays
• How to Calculate Working Days in a Month in Excel

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