How to Calculate Workdays in Excel – 3 Useful Formulas

In this article, we will explore the following functions to be able to calculate workdays in Excel –

Let us look at 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

 

NETWORKDAYS

Determining the count of working days manually can be quite challenging. It would be quite difficult and time-consuming to check your calendar and count weekdays week after week. Fortunately, Excel’s NETWORKDAYS Formula simplifies this task.

The NETWORKDAYS function returns the number of whole working days between start_date and end_date. It excludes the weekends in the count, and you can also provide a list of holidays for it to exclude as well in the count.

The syntax for this function is –

=NETWORKDAYS(start_date, end_date, [holidays])

where,

• start_date – A date that represents the start date. Required.
• ending date – A date that represents the end date. Required.
• [holidays] – It contains a list of holidays that you need to exclude from your working calendar.

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

Follow the steps below to calculate the workdays in Excel –

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

STEP 2: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS(A2

STEP 3: Enter the second argument i.e. end_date. It is mentioned in cell B2.

=NETWORKDAYS(A2,B2)

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

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

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

=NETWORKDAYS(A2,B2,F2:F12)

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

 

NETWORKDAYS + EOMONTH

You have the start dates mentioned in column A and you want to know the workdays in a month. For this, you can use the EOMONTH function to get the last day of the month which is the indicated number of months before or after start_date.

This formula is useful when you want to know the number of working days in a month. Instead of typing the end date of each month individually, you can simply use the EOMONTH function to get the end date and then use NETWORKDAYS formula to get the number of working days in that month.

The syntax of EOMONTH is –

=EOMONTH(start_date, months)

where,

• start_date – A date that represents the start date. Required.
• months – The number of months before or after start_date. A positive value for months returns a future date; a negative value returns a past date.

Follow the steps below to get the number of working days in a month –

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

STEP 2: Enter the first argument i.e. start_date. It is mentioned 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 i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS(A2,EOMONTH(A2

STEP 4: Enter the second argument i.e. months. It should be 0 as you want to know the days in that month itself.

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

STEP 5: 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 date.

 

NETWORKDAYS.INTL

The NETWORKDAYS.INLT is a flexible version of the NETWORKDAYS formula. It is used to calculate the working days between the start and end date and additionally, it allows you to specify which days and how many of them 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 like Sunday-Monday, Sunday-only, etc.

The syntax of this formula is –

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

• start_date – A date that represents the start date. Required.
• ending date – A date that represents the end date. Required.
• [weekend] – It indicates the days of the week that are weekend days.
• [holidays] – It contains a list of holidays that you need to exclude from your 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 the steps below to get the number of working days in a month considering only Sunday as non-working day –

STEP 1: Enter the NETWORKDAYS.INTL formula.

=NETWORKDAYS.INTL

STEP 2: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS.INTL(A2

STEP 3: Enter the second argument i.e. end_date. It is mentioned in cell B2.

=NETWORKDAYS.INTL(A2,B2

STEP 4: Enter the third argument i.e. weekend. It should be 11 as Sunday is considered a non-working day.

=NETWORKDAYS.INTL(A2,B2,11

STEP 5: Enter the fourth argument i.e. holidays. It is mentioned 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 date mentioned excluding Sundays.

 

Conclusion

You can harness the capabilities of Microsoft Excel for workday calculations and bring efficiency and accuracy to project management and scheduling. Whether utilizing the basic NETWORKDAYS function, incorporating EOMONTH for monthly analyses, or customizing workweek configurations with NETWORKDAYS.INTL, Excel has an in-built function for each purpose.

Download our 141 Free Excel Templates and Spreadsheets.