What is Excel NETWORKDAYS.INTL Function?
Excel NETWORKDAYS.INTL function comes under Excel Date and Time functions. It calculates the number of whole work days between two specified date, and excludes weekends and holidays.
It allows you to specify any days for the weekend and holidays.
This function is available from Excel 2010 version and so it is not available in earlier versions of Excel.
It is similar to the Excel NETWORKDAYS function which can be found in earlier versions.
How Excel NETWORKDAYS.INTL Function Works?
Excel NETWORKDAYS.INT function returns a positive integer that represents the total number of working days between the two dates that are specified, and excludes weekends and any dates identified as holidays.
The weekends can be any day other than Saturday and Sunday.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Input Arguments as follows:
- start_date – a date value that represents the start date, from where to start counting the number of workdays
- end_date – a date value that represents the end date, where to count the number of workdays to.
- [weekend] – This is an optional argument. It specifies which weekdays can be counted as weekend. You can specify the weekend here if you have other days apart from Saturday or Sunday. If it is omitted, Saturday and Sunday are taken as weekends and will not be counted.
It can be a number or a string. The number values for [Weekend] arguments are:
The string values for [Weekend] argument consist of a series of seven 0’s and 1’s that represents the seven days of the week. It starts from Monday and ends on Sunday.
Each 0 represents working days while Each 1 represents a weekend. See as explained in the additional notes.
- [holidays] – This is also optional. It is a range of dates that are not counted with the workdays. These are usually public holidays. The holidays could be entered as a reference to a range of cells that contain the holiday dates or could be entered as an array of serial numbers that represents the dates.
When Excel NETWORKDAYS.INTL Function is Used
NETWORKDAYS.INTL in Excel is used when you want to calculate the number of whole working days between two specified dates. It does not count the weekends and holidays, which the user specifies in the function.
For example, when you want to calculate the over-time benefits that accrue to employees, NETWORKDAYS.INTL is the function to use.
And with this function, you can use any day, apart from Saturday or Sunday for weekends because it allows you to specify any days for weekends. For example, you can specify Thursday or Friday as weekends, or only Sunday as weekend.
- A date can be entered as a date stored as text or a date entered as text (in double quotes).
- Any single day or two consecutive days could be specified as weekend. If you do not specify any day for weekend, Excel will automatically take Saturday and Sunday as weekend. When you enter the weekend argument, Excel displays a drop-down menu for you to select the relevant option.
- For part-time jobs or jobs that have non-consecutive working days, you will enter the following format in the [Weekend] section of the formula:
- If you work only Mondays, Wednesday and Fridays, type in “0101011” (Working days are represented by 0, and non-working days represented by 1).
- In this format, the first number represents Monday and the last number represents Sunday.
- When using these format, the numbers should be in quotes
- Using the same logic, “1011011” indicates only 2 working days as Tuesday and Friday, and the other 5 days are non-working days.
- Using the string “1111111” is not valid.
How to Use Excel NETWORKDAYS.INTL Function: Formulas & Examples
We will show three examples of how to use Excel NETWORKDAYS.INTL function.
The spreadsheet shows three different examples of Excel NETWORKDAYS.INTL formulas used to calculate the number of work days between two specified dates as shown in the 3rd, 4th and 5th column above.
The date listed in the spreadsheet is for each month in the year 2020, and each month starting on the 1st and ending on the last day of the month which is either 28th, 30th or 31st.
1. When Weekend and Holiday is not Specified/Omitted in the Formula
We use the formula below:
In this formula (Column C), NETWORKDAYS.INTL specifies only the start date and end date, and does not specify the [weekend] or [holidays] array in the formula.
Here, NETWORKDAYS.INTL returns the total number of working days, including the holidays.
Note that this formula automatically takes Saturday and Sunday as weekends and so excludes them from the calculation of number of work days.
2. When Weekend and Holiday is Specified
To specify the weekend and holiday arguments in NETWORKDAY.INTL function, use this formula:
=NETWORKDAYS.INTL(A7, B7, 1, $G$7:$G$17)
Using this formula (Column D), NETWORKDAYS.INTL specifies the start date, end date, weekend and holiday.
It returns the total number of workdays and does not count the weekends and holidays specified in the formula.
In this formula, the number 1 is used which indicates the weekend as Saturday and Sunday as displayed in the number values. The holiday section in the formula references the cell range that contains the holiday dates.
3. Enter Weekend Argument as a String
Another way to specify the weekend argument in Excel NETWORKDAY.INTL function is to enter the value as a string like this:
=NETWORKDAYS.INTL(A7, B7, “0001011”, $G$7:$G$17
As explained above, the weekend argument can take a number value or string.
Again, in this formula, the weekend is specified using the string “0001011” which indicates Thursdays, Saturdays and Sundays are weekends, and the holidays are specified by referencing the cell range G7:G17.
(See additional notes section above for more explanation on the string format).
Common Errors in Excel NETWORKDAYS.INTL Function
Common errors you can find in Excel NETWORKDAYS.INTL function are:
- #NUM! – This occurs if the [weekend] argument specified is a number that is invalid.
- #VALUE! – This occurs if:
- the supplied start_date, end_date or any values specified for [holidays] are not valid dates;
- the text string specified for [weekend] argument is invalid.
Other Related Excel Functions
Thank you for reading Excel NETWORKDAYS.INTL function. You can master Excel by learning other Excel functions.
Click below to check out these other functions in Excel:
- Excel EDATE Function