Excel NETWORKDAYS.INTL Function to Calculate Work Days

The NETWORKDAYS.INTL is a Date & Time function that calculates and returns the number of workdays between two given dates. The NETWORKDAYS.INTL function allows you to specify which days of the week are weekends and also use a list of holidays. In this guide, we’re going to show you how to use the NETWORKDAYS.INTL function to find the number of business days between two dates, and also go over some tips and error handling methods.

Supported Versions

The NETWORKDAYS.INTL function was first introduced in Excel 2010 and has been a feature in all later versions. It is supported in Excel 2013, Excel 2016, and Excel 2019, ensuring broad compatibility across different iterations of the software. Users of Microsoft's subscription service, Excel for Microsoft 365, also have access to this function. Furthermore, it's available in Excel for Mac 2011 and the more recent Excel for Mac for Microsoft 365. Additionally, NETWORKDAYS.INTL is also supported in Excel Online.

NETWORKDAYS.INTL Function Syntax

By default, Saturday and Sunday are considered weekends, but you can change this according to your needs. It can be specified in two ways:

Configuring Weekends Using Numbers:

You can use the number codes for the [weekend] argument if you'd like to count weekends as a single day or two consecutive days. See below for more options.

Configuring Weekends Using String

Alternatively, you can enter a 7-character string that indicates the business days and weekends. The days start with Monday and you can use zeros and ones (0, 1) which represent a business day and a no work day respectively. For example:

• 0000110 indicates that Fridays and Saturdays are non-workdays.
• 0000011 indicates that Saturdays and Sundays are non-workdays.

This approach allows for a more intuitive and visual representation of the workweek. It eliminates the need to remember specific numeric codes for different weekend configurations, which can be particularly helpful if you frequently deal with varying workweek patterns. Instead of recalling various number codes for different weekend setups, you can directly visualize the days of the week and set your workdays and weekends accordingly.

Furthermore, the string format offers a higher degree of customization. While numerical codes cover many common configurations of weekends, they may not accommodate every possible scenario. The string method, however, allows for any combination of workdays and weekends, providing a level of precision and customization that numerical codes cannot match.

Formula Example: Finding the Number of Workdays Between Two Dates

The main application of the Excel NETWORKDAYS.INTL function is in determining the count of workdays within a given date range. This capability is particularly crucial in several professional scenarios, such as orchestrating project timelines or monitoring the progress against deadlines. By enabling precise calculation of working days between two specific dates, this function becomes an indispensable tool for effective planning and time management in business environments.

Only weekends (Saturday and Sunday) excluded

Saturdays and Sundays are weekends by default. To find the number of business days excluding only the default weekend days, simply enter a value for the start_date and end_date arguments. Both date values can be a string, a serial number, or a calculated date by a formula (e.g. DATE). Here is an example:

Custom weekends and holidays excluded

Custom weekend (non-working) days can be specified by entering [weekend] and [holidays] arguments.

Use one of the predefined numbers or a string code to specify which days are to be excluded as weekends.

• INTL(DATE(2019,10,1),DATE(2019,11,30),5) assumes that Wednesday and Thursday are weekend days.
• INTL(DATE(2019,10,1),DATE(2019,11,30),"0101010") excludes Tuesday, Thursday, and Saturday.

You can provide an array of dates for the [holidays] arguments to exclude specific dates in addition to the weekends. The [holiday] argument can be a range of cells or a constant array.

Excel NETWORKDAYS.INTL vs. NETWORKDAYS to Calculate Workdays Between Two Dates

The NETWORKDAYS.INTL and NETWORKDAYS formulas in Excel serve a similar fundamental purpose - they both calculate the number of business days between two dates. However, there are distinct differences between the two, particularly in terms of flexibility and customization options available for defining weekends and holidays.

The original Excel NETWORKDAYS function is more straightforward but less flexible compared to NETWORKDAYS.INTL. It automatically assumes Saturdays and Sundays as the standard weekend days. This assumption aligns well with the typical Monday-to-Friday workweek, making NETWORKDAYS a suitable choice for standard business calculations. Additionally, it allows the user to define a list of holidays which will be excluded from the count of business days. This simplicity makes NETWORKDAYS ideal for users who work with a traditional workweek structure and need a quick and uncomplicated way to calculate working days.

Here are some scenarios when you should use NETWORKDAYS.INTL over NETWORKDAYS function:

Non-Standard or Variable Weekends: The primary reason to use NETWORKDAYS.INTL over NETWORKDAYS is when you're dealing with non-standard or variable weekends. NETWORKDAYS assumes a fixed Saturday-Sunday weekend, which is common in many regions and industries. However, if your workweek includes different days as weekends – for example, in some Middle Eastern countries where the weekend is Friday-Saturday, or in cases where weekends vary due to shift schedules – NETWORKDAYS.INTL is the better choice. It allows for complete customization of weekend days, either through specific numeric codes or a seven-character string representing each day of the week.

Complex Scheduling Requirements: NETWORKDAYS.INTL is also preferable in scenarios requiring more complex scheduling, where weekends might not be two consecutive days or might change over time. This flexibility is particularly useful in industries like healthcare, hospitality, or emergency services, where work schedules do not follow the traditional Monday-to-Friday pattern.

Global and Multicultural Environments: For businesses operating in multiple countries or with diverse workforces, NETWORKDAYS.INTL can handle various regional workweek patterns. This adaptability makes it a valuable tool for international project management, where team members might have different standard weekends.

Excel WORKDAY.INTL vs. NETWORKDAYS.INTL Functions

The WORKDAY.INTL and NETWORKDAYS.INTL functions in Excel, though similar in their consideration of business days, have distinct purposes and usage scenarios that cater to different needs in date and time management.

The primary purpose of the Excel WORKDAY.INTL function is to compute a future or past date based on a specific number of business days. It's designed to navigate through the business calendar, skipping weekends and optionally recognized holidays, to arrive at a target date.

WORKDAY.INTL formula is commonly used in project management for calculating deadlines or expected completion dates. For instance, if a project starts on a certain date and is expected to take 15 working days, WORKDAY.INTL can determine the exact finishing date. It's also used in finance for calculating settlement dates or in HR for determining due dates for employee-related activities. The flexibility in defining weekends (e.g., Friday-Saturday as weekends in some regions) and the option to exclude holidays make it invaluable for international businesses and diverse work environments.

Error Handling in NETWORKDAYS.INTL Function

Here are typical errors and their handling methods in Excel NETWORKDAYS.INTL formula:

#VALUE! Error: This error occurs when NETWORKDAYS.INTL function encounters invalid date formats or non-date values in the start_date, end_date, or holidays arguments. To handle this error, ensure that the start_date and end_date are valid Excel dates. You might need to convert text or serial numbers to date format using the DATE function. Also, check the holidays argument for valid dates and correct any non-date values or incorrect formats.

#NUM! Error: This error is triggered when the weekend argument is out of the acceptable range or when the start_date is later than the end_date. Verify that the weekend parameter is within the correct range (1 to 17 for number codes or a seven-character string consisting of 0s and 1s). Also, check that the start_date is earlier than or equal to the end_date. Swap the dates if necessary.

Sometimes, Excel NETWORKDAYS.INTL formula may not return an error but still produce incorrect results. This often happens due to incorrect setup of weekend codes or strings. Double-check your weekend configuration to ensure it matches your intended non-working days. Overlooking holidays that fall on weekends also causes incorrect results. Remember that if a holiday falls on a weekend and is shifted to a weekday, it should be included in the holidays argument to ensure accurate calculations.

Another common mistake is when your date inputs are in text format. Excel may not recognize them as valid dates, leading to errors. Convert any text inputs to date format using the DATE function or Excel's date recognition capabilities.

NETWORKDAYS.INTL Tips

• Use the NETWORKDAYS function if you are using Saturday and Sunday for no business days.
• NETWORKDAYS.INTL function ignores time value of dates.
• Excel keeps date and time values as numbers. Excel assumes that Jan 1st, 1900 is 1, and every subsequent date value is based on this. While whole numbers represent days, decimals represent time values. For example; 1/1/2018 is equal to 43101, and 12:00 is equal to 5.