Eric Campbell 5 min read Excel

How to Use NETWORKDAYS in Excel

Excel's NETWORKDAYS function solves a problem every project manager, HR professional, and business analyst faces: calculating the actual working days between two dates. Unlike simple date subtraction...

Key Insights

  • NETWORKDAYS automatically excludes weekends when calculating business days between dates, and accepts an optional holidays parameter to exclude specific dates like company holidays or national observances
  • NETWORKDAYS.INTL extends the basic function with customizable weekend patterns, supporting any two-day weekend combination or even custom work week configurations using binary masks
  • Combining NETWORKDAYS with TODAY(), IFERROR, and conditional formatting creates dynamic project tracking systems that automatically update remaining workdays and flag deadline risks

Introduction to NETWORKDAYS

Excel’s NETWORKDAYS function solves a problem every project manager, HR professional, and business analyst faces: calculating the actual working days between two dates. Unlike simple date subtraction that treats every day equally, NETWORKDAYS understands that businesses operate on schedules that exclude weekends and holidays.

The function matters because business timelines depend on working days, not calendar days. When you promise a deliverable in “10 business days,” you’re not counting Saturdays, Sundays, or the Thanksgiving holiday. NETWORKDAYS handles this calculation automatically, saving you from manual counting or complex workarounds.

Common use cases include project timeline calculations, employee working day tracking for payroll or leave management, SLA compliance monitoring, and deadline planning. If your work involves scheduling, resource allocation, or time-based metrics, you’ll use this function regularly.

Basic NETWORKDAYS Syntax and Usage

The NETWORKDAYS function follows a straightforward structure:

=NETWORKDAYS(start_date, end_date, [holidays])

Parameters:

  • start_date: The beginning date of your period (required)
  • end_date: The ending date of your period (required)
  • holidays: A range of dates to exclude from the calculation (optional)

The function counts both the start and end dates as working days if they fall on weekdays. It automatically excludes all Saturdays and Sundays.

Here’s a basic example calculating project duration:

=NETWORKDAYS("1/3/2024", "1/31/2024")

This returns 21, representing the working days in January 2024 (excluding weekends). You can also reference cells instead of hard-coding dates:

=NETWORKDAYS(A2, B2)

Where A2 contains your start date (1/3/2024) and B2 contains your end date (1/31/2024). Cell references make your formulas dynamic and reusable across multiple rows.

Excluding Holidays from Calculations

The real power of NETWORKDAYS emerges when you factor in holidays. Most businesses observe specific days off beyond standard weekends, and these should be excluded from working day calculations.

First, create a holidays list in your spreadsheet. I recommend placing this in a dedicated area or separate sheet:

Holidays (Column H)
1/1/2024  (New Year's Day)
1/15/2024 (MLK Jr. Day)
2/19/2024 (Presidents Day)
5/27/2024 (Memorial Day)
7/4/2024  (Independence Day)
9/2/2024  (Labor Day)
11/28/2024 (Thanksgiving)
12/25/2024 (Christmas)

Now reference this range in your NETWORKDAYS formula:

=NETWORKDAYS(A2, B2, $H$2:$H$9)

The dollar signs create an absolute reference, so when you copy the formula down, it always points to the same holiday list. For a project running from 1/3/2024 to 1/31/2024, this formula returns 20 instead of 21, because it now excludes New Year’s Day.

For cleaner formulas, define a named range for your holidays. Select H2:H9, then in the Name Box (left of the formula bar), type “CompanyHolidays” and press Enter. Your formula becomes:

=NETWORKDAYS(A2, B2, CompanyHolidays)

This approach improves readability and makes formula maintenance easier. Update the named range once, and all formulas using it automatically reflect the changes.

NETWORKDAYS.INTL for Custom Weekends

NETWORKDAYS assumes Saturday-Sunday weekends, which works for most Western businesses but fails for companies operating on different schedules. NETWORKDAYS.INTL provides flexibility for international operations or non-standard work weeks.

The syntax adds a weekend parameter:

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

The weekend parameter accepts numeric codes representing different two-day weekend combinations:

  • 1 or omitted: Saturday-Sunday (default)
  • 2: Sunday-Monday
  • 3: Monday-Tuesday
  • 7: Friday-Saturday
  • 11: Sunday only
  • 17: Saturday only

For a Middle Eastern business operating Sunday through Thursday with Friday-Saturday weekends:

=NETWORKDAYS.INTL(A2, B2, 7, CompanyHolidays)

For even more control, use a binary string where 1 represents a weekend day and 0 represents a workday, starting with Monday:

=NETWORKDAYS.INTL(A2, B2, "0000011")

This represents Monday through Friday as workdays (0) and Saturday-Sunday as weekends (1). For a four-day work week (Monday-Thursday), use:

=NETWORKDAYS.INTL(A2, B2, "0000111", CompanyHolidays)

This marks Friday through Sunday as non-working days.

Practical Applications and Advanced Techniques

NETWORKDAYS becomes significantly more powerful when combined with other Excel functions. Here are practical implementations you can deploy immediately.

Dynamic deadline tracking shows remaining workdays until a project due date:

=NETWORKDAYS(TODAY(), C2, CompanyHolidays)

Where C2 contains your deadline date. This formula automatically updates daily, showing how many working days remain. Pair it with conditional formatting to highlight approaching deadlines:

  • Select your formula column
  • Conditional Formatting > New Rule > Format cells that contain
  • Cell Value less than or equal to 5
  • Format with red fill

Employee working days calculation for payroll or leave tracking:

=NETWORKDAYS(B2, C2, CompanyHolidays) - D2

Where B2 is the pay period start, C2 is the pay period end, and D2 contains any personal leave days taken. This gives you the actual days worked for compensation calculations.

Project buffer analysis compares planned versus actual working days:

=NETWORKDAYS(A2, B2, CompanyHolidays) - E2

Where E2 contains the estimated working days for a task. Positive results indicate schedule buffer; negative results flag delays.

Workload distribution calculates daily capacity requirements:

=F2 / NETWORKDAYS(A2, B2, CompanyHolidays)

Where F2 contains total hours required. This divides total effort across available working days to determine daily hour requirements.

Common Errors and Troubleshooting

NETWORKDAYS generates specific errors when something goes wrong. Understanding these helps you debug formulas quickly.

#VALUE! Error occurs when Excel doesn’t recognize your dates. This typically happens with text-formatted date cells or invalid date entries. Solution:

=IFERROR(NETWORKDAYS(DATEVALUE(A2), DATEVALUE(B2), CompanyHolidays), "Invalid Date")

DATEVALUE converts text strings to proper date values. IFERROR catches any remaining issues and displays a custom message.

#NUM! Error appears when your start date is after your end date. For scenarios where date order might vary, use:

=NETWORKDAYS(MIN(A2, B2), MAX(A2, B2), CompanyHolidays)

MIN and MAX ensure the earlier date is always the start date, regardless of which cell contains which value.

Incorrect results often stem from date formatting issues. Ensure your date columns use Excel’s Date format (not General or Text). Right-click the column > Format Cells > Date.

Holiday range errors happen when your holidays list contains empty cells or non-date values. Clean your holiday range:

=NETWORKDAYS(A2, B2, IF(ISBLANK(CompanyHolidays), "", CompanyHolidays))

Or better yet, maintain a clean holidays list without gaps.

Zero or negative results indicate data problems. Wrap your formula in error checking:

=IF(NETWORKDAYS(A2, B2, CompanyHolidays) < 0, "Check Dates", NETWORKDAYS(A2, B2, CompanyHolidays))

For production spreadsheets, combine multiple error checks:

=IFERROR(
    IF(A2="", "Missing Start Date",
    IF(B2="", "Missing End Date",
    IF(NETWORKDAYS(A2, B2, CompanyHolidays) < 0, "End Before Start",
    NETWORKDAYS(A2, B2, CompanyHolidays)))),
    "Invalid Dates")

This nested formula checks for missing dates, date order issues, and invalid date formats, providing specific error messages for each scenario.

NETWORKDAYS is essential for any business operating on working day schedules. Master the basic function first, then layer in holiday exclusions and custom weekends as needed. Combine it with TODAY() for dynamic tracking, wrap it in IFERROR for robust error handling, and pair it with conditional formatting for visual deadline management. These techniques transform a simple date function into a powerful project management and scheduling tool.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.