How to Use NETWORKDAYS to Calculate Working Days in Excel
=NETWORKDAYS(start_date, end_date, [holidays])The NETWORKDAYS function is one of Excel's most practical date calculation tools, designed specifically for business environments where you need to count only working days. Whether you're managing project timelines, calculating employee work schedules, or determining delivery dates, NETWORKDAYS eliminates the manual counting of weekdays and automatically excludes weekends and specified holidays. This formula is essential for HR departments, project managers, and anyone working with time-sensitive business calculations. Unlike the simple DAYS function which counts all calendar days, NETWORKDAYS intelligently filters out Saturdays and Sundays by default, and allows you to exclude additional holidays through an optional parameter. This makes it invaluable for creating accurate project schedules, calculating work hours, determining turnaround times, and generating realistic deadline estimates. Understanding how to leverage NETWORKDAYS effectively can significantly improve your project planning accuracy and resource allocation decisions.
Syntax & Parameters
The NETWORKDAYS function follows a straightforward syntax: =NETWORKDAYS(start_date, end_date, [holidays]). The start_date parameter is required and represents the initial date from which you want to begin counting working days. The end_date parameter is also required and marks the final date for your calculation. Both dates must be valid Excel date values, either entered as date literals (using DATE function) or as cell references containing dates. The optional [holidays] parameter allows you to specify a range of cells containing dates that should be excluded from the count. This is particularly useful for accounting for company holidays, vacation days, or other non-working periods. You can reference a single cell, a range like A1:A10, or even multiple non-contiguous ranges. If you omit the holidays parameter, NETWORKDAYS will only exclude weekends (Saturday and Sunday). It's important to note that NETWORKDAYS always excludes Saturdays and Sundays by default; if your organization operates on different weekend days, you'll need to use NETWORKDAYS.INTL instead. The function returns an integer representing the total number of working days between your specified dates, inclusive of both the start and end dates if they fall on working days.
start_dateend_dateholidaysPractical Examples
Basic Project Timeline Calculation
=NETWORKDAYS(B2,B3)This formula calculates working days between the start date in B2 and end date in B3, automatically excluding weekends. If B2 contains 2024-01-15 and B3 contains 2024-01-26, the result would be 8 working days (excluding the Saturday and Sunday that fall within this period).
Delivery Timeline with Company Holidays
=NETWORKDAYS(A2,A3,E2:E8)This formula counts working days from the order date (A2) to the target delivery date (A3), while excluding the company's holiday dates stored in the range E2:E8. If holidays include New Year's Day, Independence Day, and Christmas, these dates will be automatically excluded from the count, providing a more accurate delivery window.
Employee Leave Approval and Return Date
=NETWORKDAYS(C5,C6,HolidayList)This formula determines working days of absence from leave start date (C5) to return date (C6), excluding company holidays stored in a named range called 'HolidayList'. If an employee takes leave from March 15-22 and March 17 is a company holiday, the result would be 5 working days of leave instead of 6, helping HR accurately assess coverage needs.
Key Takeaways
- NETWORKDAYS calculates working days between two dates, automatically excluding weekends (Saturday and Sunday) and any specified holidays
- The function includes both start and end dates in its count if they fall on working days, making it inclusive for project planning calculations
- Use the optional holidays parameter to exclude company holidays, vacation days, or any other non-working periods by referencing a range of dates
- For custom weekend patterns (such as Friday-Saturday or different regional weekends), use NETWORKDAYS.INTL instead of the standard NETWORKDAYS function
- NETWORKDAYS is ideal for HR leave calculations, project timeline management, delivery date estimation, and any business scenario requiring accurate working day counts
Pro Tips
Create a dynamic holiday list using a named range that updates automatically. Define a named range called 'CompanyHolidays' pointing to your holiday dates, then reference it in formulas as =NETWORKDAYS(A1,A2,CompanyHolidays). This way, you can add or remove holidays without editing individual formulas.
Impact : Saves time on formula maintenance and ensures consistency across all your calculations. When holidays change, you update one list instead of dozens of formulas.
Use NETWORKDAYS in combination with conditional formatting to highlight projects that have fewer than a certain number of working days remaining. For example, highlight cells where =NETWORKDAYS(TODAY(),EndDate,Holidays)<5 to identify tight deadlines.
Impact : Provides visual alerts for critical timelines, helping you prioritize work and allocate resources more effectively to at-risk projects.
When calculating working days for multiple time zones or international teams, remember that NETWORKDAYS only excludes Saturday and Sunday. Create separate calculations using NETWORKDAYS.INTL for teams with different weekend schedules, then sum them for total working capacity.
Impact : Ensures accurate capacity planning for global teams and prevents scheduling conflicts due to different weekend conventions across regions.
Combine NETWORKDAYS with IFERROR to handle edge cases gracefully: =IFERROR(NETWORKDAYS(A1,A2,Holidays),"Invalid dates"). This prevents error messages from displaying in your reports and provides user-friendly feedback.
Impact : Improves spreadsheet professionalism and prevents confusion when users encounter invalid date inputs or formula errors.
Useful Combinations
Calculate Project Completion Date with Remaining Days
=WORKDAY(TODAY(),NETWORKDAYS(B2,B3))This combination first calculates the working days needed for a project using NETWORKDAYS, then uses WORKDAY to determine when that project will actually complete starting from today. This is useful for re-baselining projects or calculating realistic completion dates when starting a task immediately.
Conditional Working Days Count Based on Status
=IF(D2="Active",NETWORKDAYS(B2,C2,Holidays),0)This formula combines IF with NETWORKDAYS to count working days only for active projects. If the project status in D2 is not 'Active', it returns 0. This is valuable for dashboard reporting where you only want to count working days for certain conditions or project states.
Calculate Average Working Days per Month
=NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,31),Holidays)/MONTH(DATE(2024,1,31))This combination uses NETWORKDAYS to count working days in a specific month, then divides by the month number to calculate average working days. Useful for capacity planning and understanding how many productive days are available per month when accounting for holidays.
Common Errors
Cause: The start_date or end_date parameters contain text values that Excel cannot recognize as valid dates, or the holidays parameter includes non-date values.
Solution: Ensure all date parameters are formatted as proper dates. Use the DATE function to create dates explicitly: =NETWORKDAYS(DATE(2024,1,15),DATE(2024,1,26)). Verify that cells in the holidays range contain only valid date values, not text strings that look like dates.
Cause: The start_date is later than the end_date, which creates a logical impossibility for the function to calculate working days forward.
Solution: Verify that your start date is chronologically before your end date. Check for reversed date references or incorrect cell assignments. Use conditional logic to ensure proper date ordering: =IF(B2<B3,NETWORKDAYS(B2,B3),NETWORKDAYS(B3,B2)).
Cause: The holidays parameter references cells that have been deleted, moved, or the range reference is broken due to sheet deletion or column removal.
Solution: Verify all cell references in your formula are valid. Use the Name Box to check if named ranges still exist. Recreate the formula with correct cell references or use a named range that won't break if columns are inserted: =NETWORKDAYS(A2,A3,INDIRECT("HolidayRange")).
Troubleshooting Checklist
- 1.Verify that both start_date and end_date are recognized as valid dates by Excel (check cell formatting is Date, not Text)
- 2.Confirm that start_date is chronologically earlier than end_date; if reversed, NETWORKDAYS will return #NUM! error
- 3.Check that all dates in the holidays parameter range are actual date values, not text strings that appear as dates
- 4.Ensure the holidays range doesn't contain blank cells or error values; clean the data before referencing it
- 5.Verify that you're using the correct function: use NETWORKDAYS for standard Monday-Friday workweeks, or NETWORKDAYS.INTL for custom weekend patterns
- 6.Test the formula with simple dates first (e.g., =NETWORKDAYS(DATE(2024,1,1),DATE(2024,1,5))) to isolate whether the issue is with date references or holiday range
Edge Cases
Start date and end date are the same day and it's a weekday
Behavior: NETWORKDAYS returns 1, counting that single working day. If the date falls on a weekend, it returns 0.
Solution: This is expected behavior and useful for checking if a specific date is a working day: =NETWORKDAYS(A1,A1) returns 1 if A1 is a weekday, 0 if weekend
Useful for validation checks in your spreadsheet logic
The entire date range falls on weekends or holidays
Behavior: NETWORKDAYS returns 0, indicating no working days within the specified range.
Solution: Verify your date range is correct. If this is unexpected, check that your dates aren't reversed and that your holiday list doesn't inadvertently exclude all days
This can occur legitimately when calculating a range that spans only a weekend or when a holiday list is too comprehensive
A holiday date appears multiple times in the holidays range
Behavior: NETWORKDAYS handles duplicates correctly and doesn't double-count; each duplicate is treated as a single exclusion.
Solution: While duplicates don't break the formula, it's good practice to remove them for data cleanliness. Use Data > Remove Duplicates or create a clean holiday list
Excel's NETWORKDAYS is intelligent enough to handle this gracefully, but data integrity is still important for maintainability
Limitations
- •NETWORKDAYS only excludes Saturday and Sunday by default; it cannot be customized to exclude different days of the week. Use NETWORKDAYS.INTL for alternative weekend configurations.
- •The function cannot account for partial working days or specific hours; it counts only full days. To calculate working hours, multiply the result by hours per day or use more complex time-based formulas.
- •NETWORKDAYS doesn't account for half-days or time-off during business hours. If your organization uses half-day leave or partial work schedules, you'll need to adjust the result manually or use supplementary calculations.
- •The holidays parameter has a practical limit on the number of dates it can efficiently reference; extremely large holiday ranges (thousands of entries) may impact performance. For very large datasets, consider filtering or using alternative calculation methods.
Alternatives
Provides flexibility to define custom weekend patterns beyond the standard Saturday-Sunday, allowing you to specify different weekend configurations for various countries and business models.
When: Use this when your organization operates on non-standard weekends, such as Friday-Saturday (Middle East), or when you need to exclude specific weekdays as working days.
WORKDAY calculates a date that is a specified number of working days away from a start date, while DAYS provides simple day counting. Using both together offers alternative date arithmetic.
When: Use WORKDAY when you need to find a future date (e.g., 'what date is 10 working days from today?') rather than counting the days between two existing dates.
Provides complete customization for counting specific days, allowing you to exclude any days of the week, not just weekends, and apply complex business logic.
When: Use this advanced approach when you need highly specific counting rules, such as excluding Mondays for maintenance or counting only specific days within a range.
Compatibility
✓ Excel
Since 2007
=NETWORKDAYS(start_date, end_date, [holidays]) - Available in all versions from Excel 2007 through Excel 365 with identical syntax and behavior✓Google Sheets
=NETWORKDAYS(start_date, end_date, [holidays]) - Google Sheets supports NETWORKDAYS with the same syntax and parameters as ExcelGoogle Sheets also supports NETWORKDAYS.INTL for custom weekend patterns. The function behavior is consistent with Excel, making spreadsheet migration seamless.
✓LibreOffice
=NETWORKDAYS(start_date, end_date, [holidays]) - LibreOffice Calc supports NETWORKDAYS with full compatibility to Excel syntax and functionality