How Does Excel NETWORKDAYS Function Work?
Excel NETWORKDAYS function calculates the number of workdays between two dates and returns a positive integer that represents the total number of working days between the two specified dates.
It does not count the weekends between the specified dates and by default, the weekend is set to Saturday and Sunday. It can also exclude any date specified as holidays.
= NETWORKDAYS(start_date, end_date,[holidays])
- start_date – a date value that represents the start date
- end_date – a date value that represents the end date
- [holidays] – This is optional. It is a range of dates, usually a national or public holiday, that are excluded from the calculation. The holidays could be entered as an array of serial numbers that represent the dates or as a reference to a range of cell that contains the dates.
When Excel NETWORKDAYS Function is Used?
When you want to get the number of working days between two specified dates, you use Excel NETWORKDAYS function. The working days excludes weekends and any date identified as holidays.
The NETWORKDAYS function is very useful in situations where you want to calculate the overtime payments or benefits accrued to employees based on number of days worked or during a project, etc.
- By default, Saturdays and Sundays are considered as weekends and so are not counted in the NETWORKDAYS function. If you want to use other days for the weekends, and not Saturday or Sunday, then use the NETWORKDAYS.INTL function.
- A date can be entered either as:
- A date stored as text
- entered as text (that is, in double quotes “ ”)
- date returned from other formulas
- If the argument is not valid, NETWORKDAYS function returns the #VALUE! error.
- The NETWORKDAYS function is not an Excel in-built function. It is part of the Analysis Tool Pack (ATP) and so, if you get a #NAME! error, it could mean that you do not have the ATP or it is not loaded properly. In such a case, it is recommended that you reload the ATP.
How to Use Excel NETWORKDAYS Function: Formulas & Examples
Example 1: Calculating the Number of days Between two dates (Includes Holidays)
NETWORKDAYS function can be used to calculate the number of days between two dates even without specifying any holiday. It automatically does not count Saturdays and Sundays.
In the above example, we calculate the total number of workdays for each month between the first day and last day of the months using the formula:
As shown in the example, it returns the total the number of days in each month, excluding Saturdays and Sundays.
In this case, there is no holiday specified in the formula.
Example 2: Calculate Number of Workdays (Excludes Holidays)
In this situation, you want to calculate the number of workdays in the months, excluding the weekends and holidays specified.
The formula to use is:
=NETWORKDAYS(A5, B5, $E$5:$E$15)
In this case, the holiday is specified using the cell reference. The dollar sign used in the formula is to lock the cell column and rows so that it can be applied to the other months when you drag to fill the formula in the other months.
Also, note that the NETWORKDAYS function will only exclude holidays that fall on a weekday. If a holiday falls on a weekend, it will be recognized as a weekend and therefore will not be counted twice.
For instance, in the example above, in the month of December, there are two holidays that fall on Saturday and Sunday. The function will not count the days only based on the fact that the days are weekends and not holiday.
Some Other Excel Functions
Thank you for reading Excel NETWORKDAYS function. You can master Excel by learning other Excel functions.
Check out some other functions in Excel:
- Excel EDATE Function