In this article, we will delve into the following methods for calculating workdays in Excel:

Table of Contents

• NETWORKDAYS
• NETWORKDAYS EOMONTH
• NETWORKDAYS.INTL

Let's examine each of these methods in detail.

Download the Excel Workbook below to follow along and understand How to Calculate Workdays in Excel –

download excel workbookWorkdays-in-a-Month-in-Excel.xlsx

Table of Contents

NETWORKDAYS

Manually counting working days can be quite challenging. It would be quite tedious and time-consuming to manually check your calendar and count weekdays week by week. Thankfully, Excel's NETWORKDAYS function simplifies this task.

The NETWORKDAYS function calculates the number of whole working days between a start_date and an end_date. It excludes weekends from the count, and you can also specify a list of holidays to exclude.

The syntax for this function is:

=NETWORKDAYS(start_date, end_date, [holidays])

where,

• start_date – A date representing the start date. Required.
• end_date – A date representing the end date. Required.
• [holidays] – A list of holidays to exclude from the working calendar.

In this example, the start date and end date are in cells A2 and B2, respectively, and the holiday list is in the cell range F2:F12.

Follow these steps to calculate workdays in Excel:

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

STEP 2: Enter the first argument, start_date, which is in cell A2.

=NETWORKDAYS(A2

STEP 3: Enter the second argument, end_date, which is in cell B2.

=NETWORKDAYS(A2,B2)

There are 66 working days between the dates of 1/1/2024 and 4/1/2024.

Now, let's include the holiday list in cell range F2:F12 as the third argument.

STEP 4: Enter the third argument – holidays, which is in cell range F2:F12.

=NETWORKDAYS(A2,B2,F2:F12)

Since 3 holidays fall between the start date and end date (1/1/2024, 1/15/2024, and 2/19/2024), there are only 63 working days.

NETWORKDAYS EOMONTH

If you have start dates listed in column A and want to calculate the workdays in a month, you can use the EOMONTH function to find the last day of the month, which is the specified number of months before or after the start_date.

This formula is useful for determining the number of working days in a month. Instead of manually entering the end date for each month, you can use the EOMONTH function to get the end date and then apply the NETWORKDAYS formula to calculate the working days in that month.

The syntax of EOMONTH is:

=EOMONTH(start_date, months)

where,

• start_date – A date representing the start date. Required.
• months – The number of months before or after the start_date. A positive value yields a future date; a negative value yields a past date.

Follow these steps to calculate the number of working days in a month:

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

STEP 2: Enter the first argument, start_date, which is in cell A2.

=NETWORKDAYS(A2

STEP 3: Enter the EOMONTH formula to get the end date.

=NETWORKDAYS(A2,EOMONTH

STEP 4: Enter the first argument, start_date, which is in cell A2.

=NETWORKDAYS(A2,EOMONTH(A2

STEP 5: Enter the second argument, months, which should be 0 to calculate the days in the same month.

=NETWORKDAYS(A2,EOMONTH(A2,0))

STEP 6: Press CTRL D to copy the formula down.

This will give you the working days between the start date and the end of the month.

NETWORKDAYS.INTL

The NETWORKDAYS.INTL function is a versatile version of the NETWORKDAYS formula. It calculates the working days between the start and end dates and allows you to specify which days should be considered non-working days. By default, it considers Saturday and Sunday as weekends.

This is particularly useful for organizations with non-standard weekends such as Sunday-Monday, Sunday-only, etc.

The syntax of this formula is:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

• start_date – A date representing the start date. Required.
• end_date – A date representing the end date. Required.
• [weekend] – Indicates the days of the week that are considered weekend days.
• [holidays] – A list of holidays to exclude from the working calendar.

The following are the weekend number values that indicate the following weekend days:

• 1 or omitted – Saturday, Sunday
• 2 – Sunday, Monday
• 3 – Monday, Tuesday
• 4 – Tuesday, Wednesday
• 5 – Wednesday, Thursday
• 6 – Thursday, Friday
• 7 – Friday, Saturday
• 11 – Sunday only
• 12 – Monday only
• 13 – Tuesday only
• 14 – Wednesday only
• 15 – Thursday only
• 16 – Friday only
• 17 – Saturday only

Follow these steps to calculate the number of working days in a month, considering only Sunday as a non-working day:

STEP 1: Enter the NETWORKDAYS.INTL formula.

=NETWORKDAYS.INTL

STEP 2: Enter the first argument, start_date, which is in cell A2.

=NETWORKDAYS.INTL(A2

STEP 3: Enter the second argument, end_date, which is in cell B2.

=NETWORKDAYS.INTL(A2,B2

STEP 4: Enter the third argument, weekend, which should be 11 to consider Sunday as a non-working day.

=NETWORKDAYS.INTL(A2,B2,11

STEP 5: Enter the fourth argument, holidays, which is in cell range F2:F12.

=NETWORKDAYS.INTL(A2,B2,11,F2:F12)

This will provide you with the number of working days between the start and end dates, excluding Sundays.

Conclusion

You can leverage the power of Microsoft Excel for workday calculations to enhance efficiency and accuracy in project management and scheduling. Whether using the basic NETWORKDAYS function, combining it with EOMONTH for monthly analyses, or customizing workweek configurations with NETWORKDAYS.INTL, Excel offers a built-in function for every need.

Download our 141 Free Excel Templates and Spreadsheets.

The above is the detailed content of How to Calculate Workdays in Excel – 3 Useful Formulas. For more information, please follow other related articles on the PHP Chinese website!