Count business days excluding holidays
IntermediateOperations teams usually plan rollouts in working days, not calendar days. For an international branch rollout, counting every date between a start and end date would overstate the schedule whenever weekends or local public holidays fall inside the project window.
The NETWORKDAYS.INTL function counts working days between two dates and lets you define which weekend pattern to use.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
For a standard Saturday and Sunday weekend, the weekend argument is 1. The holidays argument can point to a range of dates that should also be excluded from the count.
To build the holiday list for each row, you can use FILTER, which returns only the values that meet a condition.
=FILTER(array, include)
In this exercise, FILTER should return only the holiday dates for the branch in the current task row. That filtered list can then be used as the holidays argument inside NETWORKDAYS.INTL.
In this sheet, you are supporting rollout plans for multiple branches. The task table is in A1:E8, and the branch holiday reference table is in A10:B19.
Your task:
Calculate the working day total for each rollout task in E2:E8 with NETWORKDAYS.INTL. Use each row's start date and end date, use 1 for a standard weekend, and use FILTER to pass only the holiday dates for that row's branch into the holidays argument.
Need some help?
Hint 1
Start by building the holidays part on its own. Ask: which cells contain the holiday dates I want returned for this row?
Hint 2
FILTER can return one column while testing another. For example, `FILTER(D2:D10, C2:C10=G2)` returns values from column D where the matching rows in column C equal the value in G2.
Hint 3
Once FILTER returns the correct holiday dates for the row, use that whole FILTER formula as the holidays argument inside NETWORKDAYS.INTL.
Related function(s)
Count business days excluding holidays
IntermediateOperations teams usually plan rollouts in working days, not calendar days. For an international branch rollout, counting every date between a start and end date would overstate the schedule whenever weekends or local public holidays fall inside the project window.
The NETWORKDAYS.INTL function counts working days between two dates and lets you define which weekend pattern to use.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
For a standard Saturday and Sunday weekend, the weekend argument is 1. The holidays argument can point to a range of dates that should also be excluded from the count.
To build the holiday list for each row, you can use FILTER, which returns only the values that meet a condition.
=FILTER(array, include)
In this exercise, FILTER should return only the holiday dates for the branch in the current task row. That filtered list can then be used as the holidays argument inside NETWORKDAYS.INTL.
In this sheet, you are supporting rollout plans for multiple branches. The task table is in A1:E8, and the branch holiday reference table is in A10:B19.
Your task:
Calculate the working day total for each rollout task in E2:E8 with NETWORKDAYS.INTL. Use each row's start date and end date, use 1 for a standard weekend, and use FILTER to pass only the holiday dates for that row's branch into the holidays argument.
Need some help?
Hint 1
Start by building the holidays part on its own. Ask: which cells contain the holiday dates I want returned for this row?
Hint 2
FILTER can return one column while testing another. For example, `FILTER(D2:D10, C2:C10=G2)` returns values from column D where the matching rows in column C equal the value in G2.
Hint 3
Once FILTER returns the correct holiday dates for the row, use that whole FILTER formula as the holidays argument inside NETWORKDAYS.INTL.