You’ll identify exactly where staffing coverage peaks and valleys — and see the gaps a manager can’t find by scrolling.
Pinecrest Urgent Care runs three shifts — Day, Swing, and Night. Nurses can work partial shifts and overlap windows exist between them. The clinic manager needs to know: for each hour of the day, how many nurses are on the floor? The scheduling system exports a start time and end time per nurse per day — but it doesn’t calculate concurrent coverage. The board meets Thursday.
| NurseID | NurseName | Date | ShiftStart | ShiftEnd |
|---|---|---|---|---|
| N-014 | Rivera | 2026-04-07 | 07:00 | 15:30 |
| N-022 | Okafor | 2026-04-07 | 14:00 | 22:30 |
| N-031 | Chen | 2026-04-07 | 22:00 | 06:30 |
| N-014 | Rivera | 2026-04-08 | 07:00 | 15:30 |
| N-019 | Patel | 2026-04-08 | 06:00 | 14:00 |
| N-033 | Williams | 2026-04-08 | 14:00 | 22:30 |
| N-022 | Okafor | 2026-04-08 | 14:00 | 22:30 |
| N-041 | Santos | 2026-04-08 | 22:00 | 06:30 |
| N-019 | Patel | 2026-04-09 | 06:00 | 14:00 |
| N-031 | Chen | 2026-04-09 | 07:30 | 16:00 |
Fill Column C (NursesOnFloor) in the Hours Template for every hour of every day.
· Monday April 7 at 15:00 should read: 4
· Sunday April 13 at 03:00 should read: 1
where HourCell is the current hour in the Hours Template (e.g. B2 containing the datetime for Monday April 7 at 15:00).
A nurse is on the floor during a given hour if their shift started at or before that hour AND their shift hasn’t ended yet. COUNTIFS lets you test both conditions across the entire schedule at once — counting only the rows where both are true. Drag the formula down 168 rows and the coverage map builds itself.