How few nurses can cover every hour of your unit?
Your unit needs 8 nurses at breakfast, 9 at midday, and 4 overnight, and every shift you post costs a different amount once evening and night differentials kick in. Which mix of start times covers every hour for the least money? This template turns that question into a one-click solve.
Operations Advanced Optimization free
After you install, this is the model to open.
How Many Nurses Does Each Shift Need?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
Starting from the usual day/evening/night roster, one click restaggers the start times:
- Optimized daily cost
- $7,648 vs $8,040 on a three-shift roster
- Nurses per day
- 20 down from 21
- Daily savings
- $392 4.9% of payroll
- Annual savings
- $143,080 at 365 days
The optimizer covers every 4-hour block with 20 nurses at $7,648 a day, while the usual three-shift roster takes 21 nurses and $8,040. That is $392 saved every day, about $143,080 a year, and no block ever dips below its minimum. Swap in your own coverage minimums and differentials, then re-run.
The model
Six overlapping 8-hour shift patterns each cover two 4-hour blocks of the day. The optimizer picks how many nurses start on each pattern so every block meets its minimum headcount.
| Day rate (7am-3pm) | $45/hr |
| Evening differential (3pm-11pm) | +$4/hr |
| Night differential (11pm-7am) | +$7/hr |
| Shift patterns | 6 overlapping 8-hour starts |
| Peak minimum (11am-3pm) | 9 nurses on duty |
| Overnight minimum (11pm-7am) | 4 nurses on duty |
| Starting roster | Day 9, Evening 8, Night 4 (21 nurses) |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Is this security control worth the budget line?Is This Security Control Worth It?
- What does that broken process actually cost per year?What a Broken Process Costs per Year
- How much should you produce each month?Production & Demand Planner
- Which risks stay red after controls?IT Risk: Still Red After Controls?
- Where should your warehouse actually be?Warehouse Location Optimizer
- Which rows in these two lists are the same company?Match Two Customer Lists That Don't Agree
Every model like this one, and the method behind them: Optimization in Google Sheets.