The NETWORKDAYS function in Microsoft Excel will calculate the number of working days between two dates. It will automatically exclude weekends (e.g. Saturday and Sunday) plus you can exclude public holidays and personal vacations.
Most people read the function as ‘Network Days’ but it’s really ‘Net Work Days’.
Figure out employee benefits from total working days accrued during a specific period. Calculating annual or long-service leave entitlements. We know one Excel user who used NetworkDays() to figure out exactly how many days he had to work until he could retire.
Alternatively, it may be used to calculate the total number of working days during a project.
Like many Office features and Excel functions, they work fine in theory but there are complications when used in the Real World™.
We’ll start by explaining the basics of Networkdays() and it’s smarter brother Networkdays.Intl() then see how to use them in a workbook.
NetworkDays syntax
The official setup of NetworkDays() in Excel is:
=NETWORKDAYS (start_date), end_date, [holidays])
- start_date – the start date
- end_date – the end date
- holidays – a range or array of one or more dates that require to be excluded from the working day’s calculation (optional)
NetworkDays.Intl is better
A newer, more flexible version called NETWORKDAYS.Intl function was introduced in Excel 2010 for Windows and Excel 2011 for Mac. According to Microsoft’s docs, NetworkDays.Intl isn’t available for the Excel Mobile apps, but we’ve used it on both Excel for iOS and Android.
We recommend using NETWORKDAYS.INTL, it will give you more options both now and in the future.
This function will still default the weekend as Saturday & Sunday; although it provides the flexibility to change the days of the week to whatever you wish to consider ‘weekend’ or even just working/non-working days of the week.
Microsoft originally added NetworkDays.Intl to support a global audience who have different definitions of ‘weekend’. These days it’s useful to everyone as the world changes to more flexible working arrangements.
NetworkDays.Intl syntax
The NetworkDays.Intl syntax in Excel is:
=NETWORKDAYS.INTL (start_date), end_date, [weekend], [holidays])
- start_date – the start date
- end_date – the end date
- weekend – which days of the week that should be considered weekends (optional)
- holidays – a range or array of one or more dates that require to be excluded from the working day’s calculation (optional)
Using NetworkDays.intl to count working days step-by-step
Define Weekend or non-working days
The weekend argument is most easily set using the codes listed in this table.
OR you can use a binary string to define a specific set of working and non-working days. This is the ultimate flexibility and great for part-time or shared jobs.
It’s seven characters (zero or one) with each position for a day of the week, starting on Monday. Zero / 0 means work day, One / 1 mean non-working day.
0000011 – is a ‘normal’ work week Mon-Fri working, Sat and Sun weekend. Same as ‘1’ in the list above.
0000110 – Friday/Saturday are days off, working Sun-Thurs. Same as ‘7’ in the above list.
0010011 – working Mon, Tues, Thurs, Fri. Off Wed, Sat and Sun.
0101010 – not working Tues, Thurs and Sat.
0000000 – a seven-day work week.
This string can be hardcoded into the function or smarties could setup a separate table of choices to control it.
Holidays
The Holidays parameter is a range or array of dates.
They could be hard-coded into the formula but normally you’ll link to a range of public holiday dates. Above is an example with a separate table of public holidays. In Networkdays.Intl() the holidays parameter can be the range F8:F13 or the table reference Holidays[Date].
Using NetworkDays.intl to count working days step-by-step
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
