Function Definition
The NETWORKDAYS function calculates the number of working days between two dates, automatically excluding weekends and specified holidays.
Parameters
- start_date (required): Start date of the period
- end_date (required): End date of the period
- [holidays] (optional): Range of holiday dates to exclude
* Recommended date format: DATE(year,month,day) e.g. DATE(2023,12,25)
Basic Syntax Example
=NETWORKDAYS("2024-01-01", "2024-01-31")Calculates workdays in January 2024 excluding Saturdays/Sundays
Here is a sample table of test data used for the NETWORKDAYS formula calculation:
Start Date End Date Formula Result Explanation
2024-01-01 2024-01-31 =NETWORKDAYS("2024-01-01", "2024-01-31") 21 Calculates working days in January 2024 (excludes weekends: 5 Saturdays & 5 Sundays)- Total days in the period: 31 days (from Jan 1 to Jan 31, 2024)
- Weekends in January 2024: 10 days (Saturdays: 6th, 13th, 20th, 27th; Sundays: 7th, 14th, 21st, 28th + 3rd Saturday/Sunday pair)
- Working days = Total days - Weekends = 31 - 10 = 21 days
Holiday Exclusion Example
=NETWORKDAYS(DATE(2023,11,1), DATE(2023,11,30), C2:C4)Where C2:C4 contains:
2023-11-23 (Thanksgiving)
2023-11-24 (Black Friday)
Here's an example table with test data for the formula =NETWORKDAYS(DATE(2023,11,1), DATE(2023,11,30), C2:C4):
Start Date End Date Holiday List (C2:C4) Formula Result Explanation
2023-11-01 2023-11-30 2023-11-10 =NETWORKDAYS(DATE(2023,11,1), DATE(2023,11,30), C2:C4) 19 Calculates working days in November 2023, excluding:
2023-11-22 - Weekends: 8 days (Saturdays: 4th, 11th, 18th, 25th; Sundays: 5th, 12th, 19th, 26th)
2023-11-24 - Specified holidays: 3 days (10th, 22nd, 24th)
Total days in period: 30 → 30 - 8 weekends - 3 holidays = 19 working days- The DATE(2023,11,1) and DATE(2023,11,30) functions define the date range as November 1-30, 2023.
- C2:C4 references 3 custom holidays within the period, all of which fall on weekdays (not weekends) to avoid double-counting.
- The result reflects actual working days after excluding both weekends and the specified holidays.
Return Value
Returns integer representing net workdays, including the start date if it's a workday. Example:
From 2023-12-18 (Monday) to 2023-12-22 (Friday) returns 5 days
Important Notes
- Default weekend: Saturday-Sunday (use NETWORKDAYS.INTL for custom weekends)
- Holidays must be valid date values
- Returns #VALUE! error for invalid dates
- Negative result when start_date > end_date
Practical Use Cases
Project Management:
Calculate deadline durations excluding public holidays
HR Applications:
Track employee service days or probation periods
Error Troubleshooting
| Error | Solution |
|---|---|
| #VALUE! | Verify date formats in all arguments |
| Inaccurate count | Check for hidden characters in holiday ranges |
Pro Tips
Create dynamic holiday calendars:
1. Use named ranges for holiday lists
2. Combine with data validation dropdowns
3. Link to official holiday calendars via Power Query