Building a weekly staff schedule by hand means juggling coverage requirements, days-off rules, and headcount constraints all at once. Most managers either overstaff to be safe or spend hours trial-and-erroring their way to something workable. There’s a better approach: let the math find the minimum staffing level automatically.
This tutorial uses SolveSheet to solve a classic workforce scheduling problem entirely inside Google Sheets.
Copy the example spreadsheet to follow along.
The Problem
Riverside Call Center needs coverage seven days a week. The minimum staff required each day varies: 5 on Monday, 6 on Tuesday and Wednesday, 5 on Thursday, 7 on Friday, 4 on Saturday, and 3 on Sunday. Every employee works five consecutive days and has two days off. The goal is to hire the fewest employees possible while meeting every day’s minimum.
There are seven possible schedule patterns depending on which day an employee starts. The solver decides how many employees to hire on each starting day.
Setting Up the Model
Decision variables — Seven cells, one per starting day (Monday start, Tuesday start, through Sunday start). These are integers: you can’t hire a fraction of a person.
Objective — Sum of all seven decision variable cells. Minimize this.
Constraints — One per day of the week. For each day, the employees on duty (determined by which start-day groups are in their five-day window) must meet or exceed the minimum. The spreadsheet uses a coverage matrix — a grid of 1s and 0s — to track which groups work which days. Each constraint formula is a SUMPRODUCT of the decision variables and the corresponding row of the coverage matrix.
Integer constraint — All decision variable cells must be whole numbers. This turns the problem from a standard LP into an integer program, which SolveSheet’s MILP solver handles automatically.
Running SolveSheet
- Open Extensions → SolveSheet.
- Set the objective cell to the total employee count and choose Min.
- Set variable cells to the seven start-day cells.
- Add seven coverage constraints: staff-on-duty ≥ minimum for each day.
- Add an integer constraint on all variable cells.
- Add a non-negativity constraint: all variables ≥ 0.
- Click Solve.
Integer programming requires the MILP solver, which is available on SolveSheet Pro. The free tier handles LP only.
Reading the Results
SolveSheet fills in the number of employees to start on each day. The Answer Report shows which days are binding — those are the days where you’re running at exactly minimum coverage. If any constraint has slack (more staff than required), you could potentially reduce headcount further if you change the schedule structure.
Try adding one more constraint — a maximum employees per start-day cap — to reflect a hiring limit. Re-solve to see how that affects the total and which days get tight.
Adapting This to Your Situation
- Change the minimum staff numbers to match your actual requirements.
- Add part-time shift options (3-day schedules, for example) by adding more decision variable columns and updating the coverage matrix.
- Add a cost-per-shift constraint if different schedule patterns have different pay rates.
Get SolveSheet
Integer programming (needed for this model) requires SolveSheet Pro. Plans start at $49/year.