totn Excel Functions

MS Excel: How to use the NETWORKDAYS Function (WS)

This Excel tutorial explains how to use the Excel NETWORKDAYS function with syntax and examples.

Description

The Microsoft Excel NETWORKDAYS function returns the number of work days between 2 dates, excluding weekends and holidays. Weekends are not considered work days and you can specify holidays to also exclude from the work day calculation.

The NETWORKDAYS function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the NETWORKDAYS function can be entered as part of a formula in a cell of a worksheet.

subscribe button Subscribe


If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the NETWORKDAYS function in Microsoft Excel is:

NETWORKDAYS( start_date, end_date, [holidays] )

Parameters or Arguments

start_date
The start date to use in the calculation and should be entered as a serial date, not a text date.
end_date
The end date to use in the calculation and should be entered as a serial date, not a text date.
holidays
Optional. It is the list of holidays to exclude from the work days calculation. It can be entered either as a range of cells that contain the holiday dates (ie: F2:F5) or as a list of serial numbers that represent the holiday dates.

Returns

The NETWORKDAYS function returns a numeric value.

Note

  • Weekends are defined as Saturday and Sunday. If you want to use different days as the weekend, try the NETWORKDAYS.INTL function.
  • If you enter a range of cells for the holidays, enter the range as an absolute value so that the range does not change when copying the formula to different cells. For example, if your holidays are stored in the range F2:F5, it is best to enter the range as $F$2:$F$5.
  • A serial date is how Excel stores dates internally and it represents the number of days since January 1, 1900.

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel NETWORKDAYS function examples and explore how to use the NETWORKDAYS function as a worksheet function in Microsoft Excel:

Microsoft Excel

In the spreadsheet above, the dates in column A, B and F have been entered as serial dates. To see the numeric value for any serial date, change the format of the cell to General.

Enter Holidays as a Range

The holidays parameter is optional and is usually entered as a range of cells. In our example, holidays are found in the range of $F$2:$F$5.

Based on the Excel spreadsheet above, the following NETWORKDAYS examples would return:

=NETWORKDAYS(A2,B2,$F$2:$F$5)
Result: 2

=NETWORKDAYS(A3,B3,$F$2:$F$5)
Result: 3

=NETWORKDAYS(A4,B4,$F$2:$F$5)
Result: 2

=NETWORKDAYS(A5,B5,$F$2:$F$5)
Result: 6

Enter Holidays as Text Dates

Holidays do not have to be entered as a range of cells. They can also be entered as text dates. However how you enter text dates depends on whether you enter one date or more than one date.

For example, here is how you would enter a single date as a holiday:

=NETWORKDAYS(A2,B2,"1/1/2016")
Result: 2

This formula would treat Jan 1, 2016 as a holiday and exclude it from the work day calculation.

If you want to enter more than one holiday as a text date, you will need to enter the dates as an array.

For example:

=NETWORKDAYS(A5,B5,{"1/1/2016","3/25/2016","3/28/2016","12/25/2016"})
Result: 6

This NETWORKDAYS function would treat 1/1/2016, 3/25/2016, 3/28/2016 and 12/25/2016 as holidays and will not include them as work days.

TIP: Just be careful when entering your dates as text values because the format of the date will depend on your Regional Settings. In our example, our Regional Settings are set to a date format of mm/d/yyyy. It is always safer to use serial dates if you are unsure.

Enter Holidays as Serial Dates

Finally, let's explore how to enter holidays as serial dates. Entering the holidays as serial dates ensures that Excel processes the dates properly. Remember that how you enter a holiday as a serial date depends on whether you are entering one date or more than one date.

For example, here is how to enter a single serial date for holidays:

=NETWORKDAYS(A2,B2,42370)
Result: 2

Since 42370 is the serial date for Jan 1, 2016, that date will be excluded from the work day calculation.

If you want to enter more than one holiday as a serial date, you need to include your serial dates as an array.

For example:

=NETWORKDAYS(A5,B5,{42370,42454,42457,42729})
Result: 6

Since 42370 is 1/1/2016, 42454 is 3/25/2016, 42457 is 3/28/2016 and 42729 is 12/25/2016, these dates will not be included as work days.

TIP: To see the numeric value for any serial date, change the format of the cell to General. It will then display the serial date value.