NETWORKDAYS in Excel – Calculate the number of work days between two dates

The Excel NETWORKDAYS and WORKDAY functions are both powerful tools for handling date and time-related calculations, particularly in the context of workdays and business schedules. While both functions are related to workdays, they serve slightly different purposes.

The WORKDAY function in Excel calculates a date a specified number of workdays in the future or past from a given starting date, excluding weekends and optionally, holidays.

The NETWORKDAYS function in Excel calculates the number of business days between two given dates, excluding weekends and optionally, specified holidays.

The Excel NETWORKDAYS function is a gem that often remains underutilised despite its potential to streamline and simplify complex date calculations. From project management to financial analysis, understanding and mastering the capabilities of Excel’s NETWORKDAYS function can significantly enhance productivity and efficiency.

In this post, we will cover:

1. What is the NETWORKDAYS function in Excel
2. The NETWORKDAYS formula
3. Practical Applications for the NETWORKDAYS function in Excel
4. Real-Life Example of the NETWORKDAYS function
5. NETWORKDAYS.INTL Function
6. Tips, Tricks and things to look out for

What is the NETWORKDAYS function in Excel?

The NETWORKDAYS function in Excel is designed to calculate the number of working days between two given dates, excluding weekends (Saturdays and Sundays) and, optionally, a list of specified holidays. This function is particularly useful for determining project timelines, analysing work schedules, and managing resources within a specific timeframe.

The NETWORKDAYS formula

=NETWORKDAYS(start_date, end_date, [holidays])

1. Start_date: The initial date from which the calculation begins.
2. End_date: The final date to which the calculation extends.
3. Holidays (optional): An optional range or array of dates representing holidays that should be excluded from the calculation.

Practical Applications for the NETWORKDAYS function in Excel

1. Project Management:

In project management, meeting deadlines is crucial. NETWORKDAYS helps calculate the number of working days required for completing tasks or the entire project. It aids in setting realistic timelines and allocating resources efficiently.

2. HR and Payroll:

For human resources departments, calculating the number of workdays for payroll or leave management is essential. Using NETWORKDAYS, you can precisely determine the number of billable workdays for payroll and manage leaves without manual counting.

3. Financial Analysis:

In financial modelling or analysing investment returns, NETWORKDAYS can be used to compute the number of business days within an investment period. This enables accurate analysis of interest accruals or cash flow over a specific time frame.

Real-Life Example of the NETWORKDAYS function

Scenario: Project Timeline Calculation

Problem: You're managing a project that involves multiple tasks and requires a precise timeline for completion. The project starts on 1st February 2023 and has a deadline of 30th April 2023. Additionally, there are specific holidays within this period that need to be accounted for.

Goal: Calculate the number of working days available for the project, excluding weekends (Saturdays and Sundays) and the specified holidays.

Given Information:

1. Project Start Date: 1st February 2023
2. Project End Date: 30th April 2023
3. Holidays within this period: 6th February 2023, 7th April 2023, 10th April 2023 and 25th April 2023

Solution using NETWORKDAYS:

Step 1: Open an Excel worksheet and label the cells for the project start date, project end date, and holiday dates.

For example:

Cell A5: Project Start Date - 01/02/2023

Cell B5: Project End Date - 30/04/2023

Cell D5 – D8: Holiday Dates - 06/02/2023, 07/04/2023, 10/04/2023 and 25/04/2023

Now, let's use the NETWORKDAYS function to calculate the total working days between the project start and end date, excluding the holidays.

Step 2: In an empty cell, say B10, enter the formula

=NETWORKDAYS(A5, B5, D5:D8)

This formula will calculate the number of working days between the start date (A5) and end date (B5), while excluding the specified holidays (D5, D6, D7 & D8).

Step 3: Excel will compute the result, providing you with the total number of working days available for your project within the specified timeframe.

The result will show the count of working days, excluding weekends and the specified holidays, giving you an accurate estimate of the time available for task execution, project planning, and resource allocation.

This calculation helps project managers to set realistic deadlines, allocate resources efficiently, and organize tasks within the given workdays, considering the non-working days due to weekends and holidays.

NETWORKDAYS.INTL Function

In some job types, the workweek might differ, such as shift-based work or businesses operating on non-standard workweeks.

The NETWORKDAYS.INTL function excludes Saturdays and Sundays by default but enables custom weekend schedules to consider different days as the weekend.

Below is a table of values given to weekend dates by Excel:

The formula for this is NETWORKDAYS.INTL(start_date,end_date,weekend,[holidays])

1. Start_date: The beginning date for the calculation.
2. End_date: The ending date for the calculation.
3. Weekend (optional): An optional code specifying which days of the week are considered weekends.
4. Holidays (optional): An optional range or array of dates representing holidays to be excluded from the calculation.

Using the same scenario from Real-life example of NETWORKDAYS function, we can calculate the workdays for alternate weekend days.

Given Information:

1. Project Start Date: 1st February 2023
2. Project End Date: 30th April 2023
3. Weekend: Monday & Tuesday (3)
4. Holidays within this period: 6th February 2023, 7th April 2023, 10th April 2023 and 25th April 2023

Solution using NETWORKDAYS.INTL:

Step 1: In cell B10 start writing the formula =NETWORKDAYS.INTL(A5,B5,

Step 2: Once you start writing the formula and get to the part for weekends, Excel will automatically bring up a list of weekends to choose from. Choose your weekend dates. For this example, we chose Monday and Tuesday.

Step 3: Finish writing the formula by selecting the holiday dates, closing the bracket and then pressing Enter.

Excel would have calculated the working days in this designated time using Monday and Tuesday as the weekend.

By using NETWORKDAYS.INTL, users have more control over defining the workdays and weekends, making it a valuable tool in scenarios where the traditional Monday-to-Friday work schedule does not apply.

Tips, Tricks and things to look out for

1. Accounting for alternate Workweeks:

Some jobs, such as shift-based work may have different weekends as opposed to the standard Saturday and Sunday weekends. If this is the case, the NETWORKDAYS.INTL function can be used to calculate the working days. Refer to NETWORKDAYS.INTL function to learn how to calculate alternate weekend working days.

2. Including Holidays:

To incorporate holidays in the calculation, ensure a separate range or array includes the holiday dates. This allows the function to accurately exclude those days from the total count of workdays.

3. Negative return value:

If you enter a start date that is later than the end date, the return value will be negative. If this happens, check the dates you have entered and adjust them accordingly.

4. #VALUE! Error:

A #VALUE! Error will be returned when either the start date, end date or any holiday dates are not a valid date.

5. The NETWORKDAYS function includes both the start and end date:

If you were to enter the same date for both the start and end date then the NETWORKDAYS function will return 1.

Conclusion

Excel’s NETWORKDAYS function is a powerful tool for simplifying and accurately calculating working days, making it an invaluable asset in various professional and personal scenarios. By mastering this function, users can efficiently manage projects, schedule tasks, and handle financial or HR-related calculations with precision.

Embracing the capabilities of NETWORKDAYS empowers Excel users to navigate the complexities of date calculations and time management, ultimately enhancing productivity and organizational efficiency.

Was this post helpful? Let us know in the Comments below.