Build the Schedule-to-Pay Engine Step by Step

Follow the steps below with your teacher. Each card mirrors the exact workflow you will replicate in Excel. Keep the spreadsheet open as you read so you can pause, copy, and test.

Step 1 — Extend the Roster

Name the table tblRoster and add columns: ShiftBlockPreference, MaxHours.
Use data validation lists inside the table (e.g., Department dropdown) so entries stay consistent.
Add a helper column IsOvertimeEligible (TRUE/FALSE). This will control the Hours sheet logic.

Step 2 — Wire the WeeklySchedule

Label time blocks down column A (use 6a-10a, 10a-2p, etc.).
Apply Format as Table → name it tblSchedule.
Set data validation: =tblRoster[EmployeeID] for each cell.
Conditional formatting rules:
- Duplicate IDs in the same column = red (double-booked day).
- Blank high-priority blocks (like 6a-10a) = gold warning.

Step 3 — Translate Blocks into Hours

Create a helper table called tblHoursRaw beneath the grid. Each row references a block and multiplies the occupied cells by the block length.

=LET(block,$A2,len,VALUE(MID(block,4,2))-VALUE(LEFT(block,2)), hours,len, ID,B2, IF(ID="",0,hours))

Push the helper rows into SUMIFS for the official Hours & Gross sheet.

Demo: Schedule Grid

Edit the cells below to see how a single dropdown change flows into the Hours Helper row. Each filled cell represents one 4-hour block. Try swapping IDs or leaving a gap to see how the helper returns 0.

	A	B	C	D	E	F	G	H
1	Block	Sun	Mon	Tue	Wed	Thu	Fri	Sat
2	6a-10a	CF01	CF01		CF03	CF03	CF03	CF03
3	10a-2p	MK01	MK04	MK04	MK04	MK02	MK02	MK02
4	2p-6p	BK01	BK02	BK02	BK02	BK02	SV01	SV01
5	6p-close	MK03	MK03	MK03	MK03	MK03	MK03	MK03
6	Hours Helper	4	4	4	4	4	4	4
7
8
9
10

Teacher Checkpoints

Roster table named and formatted (no white cells outside the table).
Schedule grid dropdowns only show IDs from tblRoster.
SUMIFS formula drafted on the Hours sheet: =SUMIFS(tblHoursRaw[Hours], tblHoursRaw[EmployeeID], A2).

Raise your hand when all three are complete so the instructor can certify alignment before moving on.

Previous: Introduction

Lesson Overview Next: Independent Practice

Phase Navigation

Hook Introduction Guided Practice Independent Practice Assessment Closing