Excel Networkdays function to exclude weekend

This tutorial explains how to use Excel Networkdays Function to exclude weekend and holiday.

Excel Networkdays function to exclude weekend and holiday

Excel Networkdays Function literally means “net working days”, which calculates the number of days between two specific days, excluding weekend (Saturday and Sunday) and holiday. Both start date and end date inclusive.

For example, from Jan 1st, 2015 (Thu) to  Jan 5th, 2015 (Monday), total number of Networkdays is three. The function counts from Thu, Fri, Mon. For Saturday and Sunday, since they are not “work days”, they do not count. For Jan 1st, 2015 (Thu), we all know it is  a public holiday, but Excel does not know that because different country has different public holiday, so you may want to define it in the holiday list, so that Excel can exclude all your defined “holiday”.

Applies to

Excel 2003 to Excel 2013

Since VBA (both Excel and Access) does not support Networkdays function, you can read the following article to recreate one

Customized Access Networkdays function

Syntax of Excel Networkdays Function – exclude weekend and holiday

NETWORKDAYS( start_date, end_date, [holidays] )

Start_date	The begin date of calculation
End_date	The end date of calculation
[Holidays]	Optional, it is the range of cells or an array that you specify as holiday (other than weekend) to be excluded from the calculation. – To input a range, type D2:D5 – To input an array, type “8/1/2015″,”13/1/2015″

Microsoft recommends to use Date Serial (such as using Date Function) instead of Text (double quote a date), click the below article if you want to know more about Date Serial.

Excel Date Function

Error of Excel Networkdays Function – exclude weekend and holiday

In older version of Excel, Excel cannot recognize Excel Networkdays Function, and the formula returns #NAME#. In that case, go to Tools > Add-Ins > Check the box “Analysis Toolpak”.

If date is not valid, #VALUE! will return

Example of Excel Networkdays Function – exclude weekend and holiday

In the below example, I highlight weekend Cells in red, and highlight Holiday Cells in orange.

Formula	Result	Explanation
=NETWORKDAYS(A2,A6)	3	5-2=3 (2 days weekend)
=NETWORKDAYS(A4,A6)	1	3-2=1 (2 days weekend)
=NETWORKDAYS(A4,A5)	0	2-2=0 (2 days weekend)
=NETWORKDAYS(A2,A19,D2:D3)	10	18-8=10 (6 days weekend + 2 days holiday)
=NETWORKDAYS(“1/1/2015″,”18/1/2015″,“8/1/2015″,”13/1/2015″)	10	Same as the above, but use Text format and Array as parameters

Limitation of Networkdays Function

Excel Networkdays Function excludes Saturday and Sunday by default. In reality, some employees may have 6 working days including Saturday, or they they take off in weekday. In this case, you need to include Saturday and Weekday, consider using NETWORKDAYS.INTL Function to customize weekday.

Outbound References

http://office.microsoft.com/en-001/excel-help/networkdays-function-HP010062292.aspx

 

Excel Networkdays function to exclude weekend