Guided project · 6 steps Cellwise Plus
Team capacity check in Excel
Work out each person's capacity for the next two weeks from working days and time off, and find who is overloaded even when the team total looks fine.
You will use: NETWORKDAYS, IF, SUM, COUNTIF
Starting data
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Person | Days off | Assigned hours | Capacity | Spare hours | Status | Sprint start | Nov 9, 2026 | |
| 2 | Maya | 0 | 52 | Sprint end | Nov 20, 2026 | ||||
| 3 | Jon | 2 | 49 | Focus hours per day | 6 | ||||
| 4 | Priya | 0 | 66 | Working days | |||||
| 5 | Luis | 1 | 61 | Team spare hours | |||||
| 6 | Dee | 5 | 20 | People over capacity |
Build it step by step
Step 1
How many working days are in the sprint? Count Monday to Friday from I1 to I2, in I4.
Step 2
Capacity is working days minus days off, times focus hours per day. Write it in D2 and fill to D6, locking I4 and I3.
Step 3
Spare hours are capacity minus assigned hours. Write it in E2 and fill to E6.
Step 4
In F2, show "Over by " and the hours (for example "Over by 6 h") when spare hours are negative, otherwise "OK". Fill to F6.
Step 5
Total spare hours for the whole team, in I5.
Step 6
How many people are over capacity? Count the negative spare hours, in I6.