NETWORKDAYS.INTL Formula

Definition

NETWORKDAYS.INTL, a dynamic Excel formula, comes in handy as a part of advanced Excel functions for data analysis. This particular formula calculates the number of working days between two dates, providing options to customize which days of the week are considered as working days.

Purpose

The primary purpose of the NETWORKDAYS.INTL formula is to compute the number of workdays between two dates. Unlike its counterpart NETWORKDAYS, the INTERNATIONAL variant gives you the flexibility to define your weekends or non-working days according to different international standards or even company-specific work schedules.

Syntax

To ensure you’re creating formulas in Excel correctly, you should follow the appropriate syntax. For NETWORKDAYS.INTL, it looks like this:

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Parameters

NETWORKDAYS.INTL uses two mandatory and two optional parameters:

  • start_date and end_date: These parameters define the period in which you want to count workdays.
  • weekend (optional): This parameter lets you define which days of the week are considered weekends or non-working days. If not provided, Excel will default to Saturday and Sunday.
  • holidays (optional): This parameter is a list of dates you want to exclude from the workdays count, such as public holidays.

Returns

The NETWORKDAYS.INTL formula provides an integer indicating the number of working days between the start_date and end_date, excluding the specified weekends and holidays.

Usage notes

This formula automatically recalculates if there are any changes to the inputs. Moreover, it considers both the start_date and end_date in the calculation. Make sure that your dates are properly formatted as dates in Excel and remember to account for any holidays and non-standard weekend days in your calculation.

Availability

The NETWORKDAYS.INTL formula is available in Excel 2010 and later versions. It forms a part of useful Excel formulas and is a part of all Excel formulas in newer versions.

Example #1

Let’s use a simple example to illustrate the use of the NETWORKDAYS.INTL formula. If we have 1st January 2023 in cell A1 and 31st January 2023 in cell B1, we can input:

=NETWORKDAYS.INTL(A1,B1)

This will return the number of workdays considering Saturday and Sunday as non-working days, and no holidays.

Example #2

In this example, we’re taking the same date range but assuming that the weekend is on Friday and Saturday, as is standard in some Middle Eastern countries:

=NETWORKDAYS.INTL(A1,B1,7)

The ‘7’ indicates that Friday and Saturday are our non-working days.

Example #3

Here, let’s add a list of holidays to the formula. If we have holidays listed in cells D1 to D3, the formula becomes:

=NETWORKDAYS.INTL(A1,B1,1,D1:D3)

This formula will return the number of working days excluding Saturdays, Sundays, and the listed holidays.

Example #4

This example shows how to use the NETWORKDAYS.INTL formula with a custom weekend string:

=NETWORKDAYS.INTL(A1,B1,"0000011")

Here, “0000011” indicates that Saturday and Sunday are considered weekends.

Example #5

In our final example, let’s consider a scenario where only Sunday is a non-working day:

=NETWORKDAYS.INTL(A1,B1,11)

Here, ’11’ signifies that Sunday is the weekend day.

Tips and Tricks

Remember, the NETWORKDAYS.INTL formula doesn’t count the end_date if it’s a non-working day. Additionally, it’s often beneficial to have a separate list of holidays in your Excel sheet for easy reference and modification.

Limitations

One limitation of NETWORKDAYS.INTL is that it only works with complete days. If you have partial days, the formula won’t calculate those accurately.

Common errors and solutions

The most common error with the NETWORKDAYS.INTL formula is due to date formatting issues. If Excel doesn’t recognize your dates, try changing the date format.

Best Practices

When using NETWORKDAYS.INTL, ensure your date and holiday inputs are in the correct format. Also, keep your weekends consistent throughout your calculations to avoid confusion.

List of Related Functions

Other related Excel functions include:

  • NETWORKDAYS: Similar to NETWORKDAYS.INTL but assumes Saturday and Sunday as weekends.
  • WORKDAY: Returns the date that is a certain number of workdays away from a start date.
  • WORKDAY.INTL: Like WORKDAY but allows customizing weekends.

Frequently Used with the Formulas

NETWORKDAYS.INTL is often used alongside the DATE and TODAY formulas to set the start_date and end_date.

Frequently Asked Questions

Q. How does NETWORKDAYS.INTL handle holidays that fall on weekends?

If a holiday falls on a weekend, it’s considered a weekend. NETWORKDAYS.INTL does not count it twice.

Q. What happens if the start_date and end_date are the same in the NETWORKDAYS.INTL formula?

If they fall on a workday, it will count as one workday. If they fall on a weekend or a holiday, it will count as zero.

Q. How to indicate weekends in the NETWORKDAYS.INTL formula?

You can either use a number from 1 to 7 or a seven-character binary string to represent your weekends.

Explore the depth of Excel’s capabilities with the NETWORKDAYS.INTL formula, a tool designed to manage and analyze date data effectively.

 

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert