How to Use Task-Specific Holiday Lists with Excel NETWORKDAYS
Question details
The user needs a way to calculate workdays for different tasks in a project schedule, where each task requires its own customized set of holiday dates rather than a generic master holiday list.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic project schedule where different tasks have different non-working days or holiday schedules.
- Observed behavior
- Instead of using a single generic holiday list, the required formula must dynamically look at the task name in one column, match it to the corresponding column header on a separate worksheet, and pull the specific holiday dates for the NETWORKDAYS calculation.
Ensure your separate holiday lists are organized clearly on a dedicated worksheet, with task names as the column headers. Familiarizing yourself with lookup functions will help you dynamically reference these dates.
Use XLOOKUP inside NETWORKDAYS to Fetch Dynamic Holiday Lists
Combine XLOOKUP with NETWORKDAYS to dynamically search for the task header and pull the correct holiday column based on the task name.
The XLOOKUP function is ideal for this scenario because it can seamlessly return an entire column of dates as a range, which the NETWORKDAYS function can then process as the [holidays] argument.
On a new sheet (e.g., 'Sheet2'), place your task names in Row 1 (e.g., A1:D1) and list the corresponding holiday dates below each task header.
Go back to your main project schedule sheet and select the cell where you want to calculate the workdays for a specific task.
Type the base function referencing your start and end dates: =NETWORKDAYS(B2, C2,
Replace the [holidays] argument with your XLOOKUP formula. Type: XLOOKUP(A2, Sheet2!$A$1:$D$1, Sheet2!$A$2:$D$20) where A2 is the task name on your main sheet.
Close the parentheses. Your final formula should look like: =NETWORKDAYS(B2, C2, XLOOKUP(A2, Sheet2!$A$1:$D$1, Sheet2!$A$2:$D$20)). Press Enter and drag the fill handle down to apply it to other tasks.
Use INDEX and MATCH for Older Versions of Excel
If you are using a version of Excel that does not support XLOOKUP, use the classic INDEX and MATCH combination to achieve the same dynamic holiday range.
Calculate Dynamic Workdays Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like NETWORKDAYS, XLOOKUP, and INDEX/MATCH, allowing you to build complex project management templates effortlessly without missing a beat.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your project schedule workbook.
- 2. Organize Holiday Data: Place your task-specific holiday dates in columns on a separate sheet with task names as headers.
- 3. Apply the Formula: Select the target cell and type the =NETWORKDAYS formula with an embedded XLOOKUP to dynamically calculate workdays based on the task name.
- 4. Save and Share: Save your file in standard .xlsx format, ensuring perfect formula compatibility with team members using different spreadsheet software.
Frequently Asked Questions
Can I use NETWORKDAYS.INTL instead of NETWORKDAYS for this setup?
Yes, NETWORKDAYS.INTL works exactly the same way but gives you the added flexibility to specify which days of the week are considered standard weekends (e.g., only Sunday). You can insert the exact same XLOOKUP or INDEX/MATCH formula into the holidays argument of NETWORKDAYS.INTL.
What happens if a task name doesn't match the holiday sheet headers exactly?
The lookup formula will return an error (such as #N/A or #VALUE!), which will cause the NETWORKDAYS function to fail. Ensure that your task names exactly match the spelling and spacing of the column headers in your holiday sheet. Using Data Validation dropdowns can help prevent spelling mistakes.
How do I handle columns that have varying numbers of holiday dates?
When referencing a standardized maximum range like $A$2:$D$50, empty cells at the bottom of shorter columns won't break the NETWORKDAYS function. Excel simply ignores the blank cells in the array when calculating holidays.
