Here’s a step-by-step example of Networkdays.Intl() in an Excel worksheet. We suggest using that improved function over the older NetworkDays() with less options.
As usual, you can select the formulas tab and Excel will guide you through the process. From Formulas | Date & Time choose NETWORKDAYS.INTL .
It will open a dialog box to select your function arguments.
As you probably know, Microsoft Excel stores dates as serial numbers so that they can be used in calculations. For instance, January 1, 1900 is serial number 1, and January 1, 2020 is serial number 43831 because it is 43,831 days after January 1,1900.
The truth behind Excel dates, time and duration
Count your working days in Excel with NETWORKDAYS()
Simple Example of NetworkDays.Intl
Calculate the total number of working days by inputting the function arguments directly into the dialog box, as shown in this example:
Better way to use NetworkDays.Intl()
It is a lot easier to create a table within Excel with all required arguments (start date, end date, holiday dates), particularly if you would like to exclude holidays as well. That way you don’t have the mess about with the formula, just change the cells which control it.
This allows you to easily amend dates in future. For this example, we will calculate the number of working days within April.
*Excel should automatically convert to a date when you type into a cell something it recognizes as a date. If not the DATE function can do the job.
Start Date: 01 April 20 =DATE(2020,4,1)
End Date: 30 April 20 =DATE(2020,4,30)
Weekends: Saturday & Sunday (1)
Holidays:
Good Friday Fri 10-Apr-20
Holy Saturday Sat 11-Apr-20
Easter Sunday Sun 12-Apr-20
Easter Monday Mon 13-Apr-20
Anzac Day Sat 25-Apr-20 (Aussie version of Veterans Day)
Insert the NETWORKDAYS.INTL formula into the required cell and select the cells accordingly. We have calculated two different results, excluding weekends only and excluding weekends and public holidays.
Weekends only
Here’s the whole formula to count working days without weekends.
=NETWORKDAYS.INTL(A2,B2,1)
Better to define names for the main variables so the formula is more readable.
=NETWORKDAYS.INTL(Date_Start,Date_End,1)
Weekend and Holidays
=NETWORKDAYS.INTL(A2,B2,1,F2:F6)
Where F2:F6 is all the holiday dates. It’s better to make the holiday list a table to take advantage of the auto-flexible listing and easier to understand.
=NETWORKDAYS.INTL(A2,B2,1,Holidays[Date])
Weekend and Holiday overlap
If a public holiday and weekend overlap it will only count as one non-working day. In the real world, when there’s an overlap between a public holiday and weekend, the following Monday may be declared a public holiday, so remember to add any ‘days in lieu’.
In this case, the two Saturday and one Sunday holidays don’t count because they are already ‘off’ days because of the weekend. There are only two ‘real’ holidays and that’s why the ‘working days with weekends and public holidays’ (D2) is 20, only 2 less than the weekends only value (C2).
Extra notes
- We’ve not forgotten about the Holiday option when it applies to ranges of dates like Vacations. It’s a lot more complicated than most NetworkDays() explanations would have you believe. We’re looking into it and will explain in an upcoming article.
- If any of the arguments you’ve provided are not entered as a valid date, NETWORKDAYS returns the dreaded #VALUE! error value. The function arguments dialog is an easy way to tell where the problem is.
- It’s easier to enter the holidays as a range of cells that contain the holiday dates (ie: F2:F6), however you can list the serial numbers too
Complete Excel NetworkDays() solution with holidays & vacations
Count your working days in Excel with NETWORKDAYS()
Using NetworkDays.intl to count working days step-by-step
Excel’s NetworkDays() and Holidays in the real world
The truth behind Excel dates, time and duration
Excel convert Range to Array in VBA
Excel merge arrays into a single array with VBA
Simple text with day, month and year to Excel date conversion
