WORKDAY that skips bank holidays
The third argument is a list of dates that are not working days.
Task:
Each job takes the number of working days in B, starting after the date in A. Weekends and the bank holidays in E2:E4 are not working days. In C2:C5 give each job's completion date.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of WORKDAY
0/3The same formula in the other shapes it takes at work.
- WORKDAY Function
Calculate working days using WORKDAY.
CoreIntermediate - WORKDAY that skips bank holidays
The third argument is a list of dates that are not working days.
Intermediate - NETWORKDAYS: working days between two dates
The reverse question: not when it ends, but how many working days it took.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start | Working days | Completion | Bank holidays | |
| 2 | 12/20/2024 | 5 | 12/25/2024 | ||
| 3 | 12/23/2024 | 3 | 12/26/2024 | ||
| 4 | 12/27/2024 | 2 | 1/1/2025 | ||
| 5 | 1/2/2025 | 4 |
What this exercise teachesMay contain the answer
Without the holiday list, every job spanning Christmas finishes two days early on paper. Keeping holidays in a range — not typed into the formula — means next year's dates are a one-cell change for every formula that uses them.