Find staffing shortfalls without netting away busy periods

A coordinator has staffing requirements and available people for four one-hour slots. Quantify unmet coverage and keep surplus time separate.

SOURCE DATA
"slot","required_people","available_people"
"09:00","3","2"
"10:00","2","3"
"11:00","4","2"
"12:00","2","2"

RULES
Each slot lasts one hour. Staff are already assigned to slots; moving them is outside this calculation.
Unmet person-hours per slot = max(required − available, 0). Surplus uses max(available − required, 0).
Do not offset a shortfall at one time with surplus at another. Count any slot with positive unmet coverage.

MY FIGURES
Unmet person-hours: __________
Slots with a shortfall: __________
Surplus person-hours: __________

MY REVIEW NOTE
________________________

METHOD
The 09:00 slot is short one person-hour; 10:00 has one surplus person-hour.
The 11:00 slot is short two person-hours, while 12:00 is exactly covered.
Total unmet coverage is three person-hours across two slots. The single surplus hour is useful context, but does not cover a different time slot.

REFERENCE ANSWERS — try first
Unmet person-hours: 3. 1 + 0 + 2 + 0 = 3.
Slots with a shortfall: 2. 09:00 and 11:00.
Surplus person-hours: 1. Only 10:00 has surplus.

Subtracting total available from total required yields two, hiding one additional uncovered hour behind surplus at a different time.

A scheduling decision also needs skill coverage, availability and break constraints. This exercise produces the shortage summary that a scheduler can inspect before reassigning anyone.

Occupational context: O*NET 31.0 Database, USDOL/ETA, Task 11363, occupation 43-5061.00. https://www.onetcenter.org/dl_files/database/db_31_0_csv/task_statements.csv
CC BY 4.0: https://creativecommons.org/licenses/by/4.0/
Original practice material and adaptations by Skill Workbench. USDOL/ETA has not approved, endorsed or tested these additions. O*NET is a trademark of USDOL/ETA.