Count your working days in Excel with NETWORKDAYS()

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

count your working days in excel with networkdays microsoft office 35018 - Count your working days in Excel with NETWORKDAYS()

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.

count your working days in excel with networkdays microsoft office 35019 - Count your working days in Excel with NETWORKDAYS()

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

subs profile e1563205311409 - Count your working days in Excel with NETWORKDAYS()
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address