How to Exclude Holidays and Task-Specific Lost Days in Excel NETWORKDAYS
Question details
Calculate accurate working days for tasks by excluding both a shared holiday schedule and task-specific non-working dates in a project timeline.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating project management timelines or Gantt charts where individual tasks have unique lost days in addition to standard company-wide holidays.
- Observed behavior
- Finding a reliable formula to subtract shared holidays using NETWORKDAYS and additionally test and subtract individual non-working days per task.
Ensure you have created a clear reference range for your shared company holidays and compiled a separate list of specific lost dates for each individual task before building your formulas.
Combine NETWORKDAYS with COUNTIF to Subtract All Non-Working Days
Use the standard NETWORKDAYS function to handle weekends and global holidays, and subtract task-specific lost days using the COUNTIFS function.
While NETWORKDAYS is perfect for removing standard weekends and global holidays from a date range, it cannot automatically handle separate unique non-working days for multiple individual tasks in the same formula. To solve this, calculate the base working days first, then deduct any task-specific lost days that fall within that task's timeframe.
List all shared company holidays in a dedicated column (e.g., cells H2:H10) to serve as your master holiday reference.
Enter the NETWORKDAYS function using your start date, end date, and global holiday range: =NETWORKDAYS(B2, C2, $H$2:$H$10).
Use COUNTIFS to count how many task-specific lost days (e.g., listed in range J2:J5) fall exactly between the task's start and end dates.
Subtract the COUNTIFS result from the NETWORKDAYS result. The final formula will look like this: =NETWORKDAYS(B2, C2, $H$2:$H$10) - COUNTIFS($J$2:$J$5, ">="&B2, $J$2:$J$5, "<="&C2).
If you are building a Gantt chart, use a conditional formatting formula like =COUNTIF($H$2:$H$10, current_date_cell)>0 to automatically highlight holidays or lost dates on your timeline.
Manage Project Timelines Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date functions like NETWORKDAYS and COUNTIFS, allowing you to accurately track complex project timelines, exclude custom holidays, and build professional Gantt charts with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your project tracker or Gantt chart document.
- 2. Enter your date formula: Select the target cell and type =NETWORKDAYS(Start_Date, End_Date, Holidays_Range) to instantly calculate working days excluding general holidays.
- 3. Subtract custom lost days: Expand the formula by appending -COUNTIFS() to dynamically subtract task-specific non-working dates directly in the formula bar.
- 4. Highlight dates visually: Navigate to Home > Conditional Formatting to apply color scales to your weekend and holiday columns for easy tracking.
Frequently Asked Questions
What is the difference between NETWORKDAYS and NETWORKDAYS.INTL?
NETWORKDAYS automatically assumes weekends are strictly Saturday and Sunday. If your project has different weekend days (e.g., Sunday and Monday, or only Friday), you should use NETWORKDAYS.INTL, which includes a fourth argument allowing you to specify exactly which days of the week are considered weekends.
Why is my NETWORKDAYS formula returning a #VALUE! error?
This usually happens if your start date, end date, or the cells in your holiday reference range are formatted as text instead of valid dates. Check your data and ensure all referenced cells are correctly formatted as Dates.
Can I use conditional formatting to highlight these excluded dates in a visual Gantt chart?
Yes. You can create a new conditional formatting rule using a formula to check if the date matches a holiday or weekend. For example, using =COUNTIF(holiday_range, current_cell_date)>0 as your rule will automatically apply a specific background color to holidays on your timeline grid.
How do I ensure I don't double-deduct a task-specific lost day that falls on a weekend?
Instead of a simple COUNTIFS, you can use an array formula or the SUMPRODUCT function combined with the WEEKDAY function to check if the task-specific lost date is a weekday before subtracting it. Alternatively, manually verify your task-specific lost dates list to remove weekends.
