Employee Schedule Template in Excel: Shift Codes, Coverage Checks, Hours, Overtime and Labor Cost
Build an employee schedule template in Excel: weekly shift grid with codes, automatic hours per person, overtime flags, coverage checks by shift, availability and time-off, labor cost, and a worked example for a five-person café.
Quick answer: an employee schedule template in Excel is a weekly grid with employees in rows and days in columns, where each cell holds a shift code (for example O = opening, M = mid, C = closing, OFF). A small shift table gives each code its start time, end time and paid hours, so formulas can calculate weekly hours per person, flag overtime, count coverage per shift and day against what you need, and estimate labor cost. Availability and time-off requests sit on their own sheet so conflicts are flagged before the schedule goes out.
The layout below works for restaurants, retail, clinics, salons and any business with shifts. Employment rules vary by state and city, so check the requirements that apply to you; the example figures are illustrative.
Workbook structure
| Sheet | Purpose |
|---|---|
| Shifts | Shift codes with start, end, unpaid break and paid hours |
| Staff | Employee name, role, hourly rate, target hours, overtime status |
| Needs | How many people you need on each shift, by day |
| Schedule | The weekly grid, hours, overtime and coverage |
| Availability | Days or shifts each person cannot work, approved time off |
Step 1: Define shift codes
| Code | Start | End | Unpaid break | Paid hours |
|---|---|---|---|---|
| O (opening) | 6:00 AM | 2:00 PM | 30 min | 7.5 |
| M (mid) | 10:00 AM | 4:00 PM | 30 min | 5.5 |
| C (closing) | 2:00 PM | 10:00 PM | 30 min | 7.5 |
| OFF | — | — | — | 0 |
Paid hours: =ROUND((End-Start)*24-Break/60,2). Name the code column Codes and the hours column CodeHours. Using codes instead of typing times keeps the grid readable and makes every total formula-driven.
Step 2: The weekly grid
On the Schedule sheet, put the week-start date in B2, dates across row 4 (=$B$2, then =B4+1) and employees down column A. Add data validation to the grid so only valid codes can be entered (Data > Data Validation > List, source =Codes).
- Weekly hours per employee (column I):
=SUMPRODUCT(COUNTIF(B5:H5,Codes),CodeHours) - Overtime hours (column J):
=MAX(0,I5-40)for non-exempt employees under the federal 40-hour weekly rule - Shifts worked:
=COUNTIF(B5:H5,"<>OFF")-COUNTBLANK(B5:H5) - Days in a row warnings, if your policy limits them, can be added with a helper row per employee.
Some states, such as California, also have daily overtime rules, and certain cities and states have predictive scheduling laws that require advance notice of schedules or pay for last-minute changes. Add the rules that apply in your location as extra checks.
Step 3: Coverage checks
Under the grid, add one row per shift code that counts how many people are scheduled each day, and compare it with the Needs sheet:
- Scheduled:
=COUNTIF(B$5:B$9,"O")(and the same for M and C) - Gap:
=Scheduled-Needed, with conditional formatting red when negative and amber when positive (possible overstaffing)
Worked example: five-person café, one week
| Employee | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Hours | OT |
|---|---|---|---|---|---|---|---|---|---|
| Alex | O | O | O | O | O | OFF | OFF | 37.5 | 0 |
| Bea | C | C | C | C | C | C | OFF | 45.0 | 5.0 |
| Chris | M | M | M | OFF | OFF | O | O | 31.5 | 0 |
| Dana | OFF | OFF | C | C | C | C | C | 37.5 | 0 |
| Eli | OFF | O | M | M | M | OFF | M | 29.5 | 0 |
| Mid shifts scheduled | 1 | 1 | 2 | 1 | 1 | 0 | 1 | ||
Check: Alex 5 × 7.5 = 37.5; Bea 6 × 7.5 = 45, so 5 overtime hours; Chris 3 × 5.5 + 2 × 7.5 = 31.5; Dana 5 × 7.5 = 37.5; Eli 7.5 + 4 × 5.5 = 29.5. Total 181 hours.
The coverage row shows the real problem: no mid shift on Saturday, usually the busiest day, while Bea is heading into overtime. Giving Bea’s Wednesday closing shift to Eli (Wednesday already has two mid shifts, so Eli’s Wednesday mid can become a close) and adding a Saturday mid for Eli fixes both issues at once: Bea drops to 37.5 hours, Eli rises to 37.0, and total hours stay at 181. This is exactly what the template is for: seeing coverage and hours together before publishing the schedule.
Step 4: Labor cost
On the Staff sheet, store each person’s hourly rate. Then in the Schedule sheet:
- Regular pay:
=(I5-J5)*Rate - Overtime pay:
=J5*Rate*1.5 - Total labor cost for the week: sum of both columns
- Labor cost as % of forecast sales:
=IFERROR(LaborCost/ForecastSales,"")
In the example, at $17.00 an hour for everyone, the week costs 181 × $17 = $3,077 at straight time, plus the overtime premium of 5 × $8.50 = $42.50, for $3,119.50. If forecast sales for the week are $12,000, labor is 26.0% of sales. Payroll taxes and benefits come on top of these wages.
Step 5: Availability and time off
On the Availability sheet, list each person’s recurring unavailability (for example “Chris — not Thursdays”) and approved time-off dates. In the grid, a conditional formatting rule can highlight conflicts:
=AND(B5<>"OFF",COUNTIFS(TimeOff[Employee],$A5,TimeOff[Date],B$4)>0)
Cells turn red when someone is scheduled on a day they have approved off. A second rule can do the same for recurring unavailable weekdays using TEXT(B$4,"ddd").
Publishing and recording actual hours
- Lock the formula areas so only the grid can be edited; see how to protect formulas in Excel.
- Share the week as a PDF or a view-only link, with the date it was published.
- Record changes after publishing (who swapped with whom, when) in a notes column.
- The schedule shows planned hours; record actual hours worked on a timesheet template, which is what payroll should be based on.
Common mistakes
- Typing times instead of codes: totals break the moment someone writes “6-2”.
- Scheduling to people instead of to demand: start from the Needs sheet, then assign people.
- Ignoring overtime until payroll: the weekly hours column should be checked before the schedule is published.
- No record of changes after the schedule is out.
- One-off copies: keep a single file with one tab per week, so history stays available.
FAQ
Can I use this in Google Sheets?
Yes. SUMPRODUCT, COUNTIF, COUNTIFS, data validation and conditional formatting all work, and staff can view the schedule on their phones.
How far in advance should schedules be posted?
It depends on your policies and, in some places, on predictive scheduling laws. Many businesses post at least a week ahead; check local requirements.
When does scheduling software make sense?
When you have many employees, multiple locations, or need shift swaps, mobile clock-in and automatic labor-law checks. The shift codes and needs table you build here translate directly into most tools.
More guides: Operations & HR.