Using NetworkDays.intl to count working days step-by-step


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

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 .

using networkdays intl to count working days step by step microsoft excel 35023 - Using NetworkDays.intl to count working days step-by-step

It will open a dialog box to select your function arguments.

using networkdays intl to count working days step by step microsoft excel 35024 - Using NetworkDays.intl to count working days step-by-step

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:

using networkdays intl to count working days step by step microsoft excel 35025 - Using NetworkDays.intl to count working days step-by-step

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.

using networkdays intl to count working days step by step microsoft excel 35026 - Using NetworkDays.intl to count working days step-by-step

*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)

using networkdays intl to count working days step by step microsoft excel 35027 - Using NetworkDays.intl to count working days step-by-step

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)

using networkdays intl to count working days step by step microsoft excel 35028 - Using NetworkDays.intl to count working days step-by-step

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’.

using networkdays intl to count working days step by step microsoft excel 35029 - Using NetworkDays.intl to count working days step-by-step

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).

using networkdays intl to count working days step by step microsoft excel 35030 - Using NetworkDays.intl to count working days step-by-step

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.

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

subs profile e1563205311409 - Using NetworkDays.intl to count working days step-by-step
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