Employee Shift Schedule Template in Excel: Hours and Coverage Example

Employee Shift Schedule Template in Excel: Weekly Hours and Coverage Example

A shift calendar can look complete while hiding a coverage gap, an unassigned closing shift, or a worker scheduled for more hours than the manager intended. For a small cleaning, pet-care, or field-service team, the useful question is not just who works Tuesday? It is which service windows are covered, how many hours are planned for each person, and what still needs confirmation?

This guide builds a weekly employee shift schedule in Excel, including an overnight-time formula, employee-hour totals, and a coverage check. The business and all shifts below are fictional. This is a planning example, not a payroll system, a staffing benchmark, or legal advice.

A schedule is not a timesheet

A schedule records intended work. A timesheet records actual hours worked. Keep both, because employees may start late, stay to handle an incident, or work during a planned meal break. Scheduled hours cannot automatically replace actual hours for pay or overtime decisions.

The US Department of Labor's overtime guidance explains that covered, nonexempt employees generally receive overtime for hours worked over 40 in a fixed workweek, subject to applicable rules and exemptions. A workweek is a recurring seven-day period, not necessarily Monday through Sunday. State and local rules may impose additional requirements.

Use this spreadsheet to plan shifts and spot potential problems. Use your approved timekeeping and payroll process to determine compensable time and actual pay.

Fictional example: Harborline Service Crew

Harborline Service Crew is an invented three-person contractor that provides reception-area and routine facility support at one office. For the fictional week of October 26–November 1, 2026, the customer requests two staffed windows on weekdays (8:00 a.m.–4:00 p.m. and 4:00 p.m.–midnight) and one morning window on Saturday and Sunday. These are invented service requirements, not general recommendations.

The supervisor assigns the following 12 shifts:

• Mon Oct 26: Ava, 08:00–16:00; Ben, 16:00–00:00.
• Tue Oct 27: Ava, 08:00–16:00; Cora, 16:00–00:00.
• Wed Oct 28: Ava, 08:00–16:00; Ben, 16:00–00:00.
• Thu Oct 29: Ava, 08:00–16:00; Cora, 16:00–00:00.
• Fri Oct 30: Ava, 08:00–16:00; Ben, 16:00–00:00.
• Sat Oct 31: Ben, 08:00–16:00.
• Sun Nov 1: Cora, 08:00–16:00.

For this example alone, every eight-hour span includes a 30-minute fully duty-free unpaid meal period, so the planned paid time is 7.5 hours per shift. The manager must arrange appropriate meal coverage; a listed break does not prove that the worker was relieved of duty.

The planned totals are Ava: 5 × 7.5 = 37.5 hours, Ben: 4 × 7.5 = 30 hours, and Cora: 3 × 7.5 = 22.5 hours. Together they equal 90 scheduled paid hours across 12 shifts. All 12 fictional required service windows have a named worker, so the assignment coverage is 12/12 = 100%. That is not a guarantee of attendance or uninterrupted on-site coverage.

Build the Shifts worksheet

Create a sheet named `Shifts` with one row per assigned shift. Use these columns:

• A — Shift date: the calendar date on which the shift starts.
• B — Employee: a consistent name or staff ID.
• C — Site or job: the location or service contract.
• D — Start time: an actual Excel time value, such as `08:00`.
• E — End time: an actual Excel time value, such as `16:00` or `00:00`.
• F — Unpaid meal minutes: enter `30` only when the planned meal period genuinely qualifies as unpaid.
• G — Scheduled paid hours: a calculated number.
• H — Assignment status: Proposed, Confirmed, or Needs replacement.
• I — Notes: access, coverage, or handover details without passwords.

For the first sample row, put October 26, 2026 in A2, Ava in B2, Office A in C2, 08:00 in D2, 16:00 in E2, and 30 in F2. Format D and E as times, not text. Repeat through row 13 for all 12 assignments.

In G2, use this formula and copy it down:

`=IF(OR(A2="",B2="",D2="",E2="",F2=""),"",IF(OR(F2<0,F2>MOD(E2-D2,1)1440),"Check break",ROUND(MOD(E2-D2,1)24-F2/60,2)))`

For an ordinary 08:00–16:00 shift, `MOD(E2-D2,1)*24` gives eight elapsed hours; subtracting 30/60 leaves 7.5. The formula also handles 16:00–00:00 because the time wraps at midnight. Microsoft's MOD documentation explains the underlying remainder function.

Important limitation: This compact formula assumes every shift is shorter than 24 hours and that the entered end time is the next occurrence of that clock time. Equal start and end times are treated as zero, not a 24-hour shift. For long shifts, daylight-saving transitions, or work crossing payroll-week boundaries, use full start and end date-times with a defined time zone and a more detailed calculation.

Test an overnight shift separately

Before relying on the workbook, enter a fictional test shift starting at 22:00 and ending at 06:00 the next morning, with a qualifying 30-minute unpaid meal. The formula should return 7.5 hours. A shift from 16:00 to 00:00 should also return 7.5.

The calculation is a planning convenience, not an automatic determination of payable hours. If the employee works during a meal period, the actual timekeeping record must reflect compensable work. The Department of Labor's hours-worked materials explain that time an employee is required or permitted to work can count as hours worked. The DOL meal-period guidance discusses bona fide meal periods. Short rest breaks can be compensable; do not automatically subtract every break.

Summarize scheduled hours by employee

Add a sheet named `Summary`. Put Ava, Ben, and Cora in cells A2:A4. In B2, enter:

`=SUMIF(Shifts!$B$2:$B$13,A2,Shifts!$G$2:$G$13)`

Copy it to B3 and B4. The expected values are 37.5, 30, and 22.5. In B5, `=SUM(B2:B4)` should return 90. Cross-check against `=SUM(Shifts!G2:G13)`, which should also return 90. If the totals differ, look for misspelled names, missing shifts, text instead of numbers, or formula errors.

You can add a planning flag in C2:

`=IF(B2>40,"Review planned hours","Within 40-hour planning screen")`

This is only a rough alert for the example's Monday–Sunday planning week. It is not an overtime eligibility or pay calculation. A worker could have additional hours at another site, a different legally defined workweek, or different state rules. Never average two weeks to avoid reviewing overtime.

Check coverage, not just totals

A total of 90 hours does not show whether the right person is present at the right time. Make a separate `Coverage` sheet listing each of the 12 required windows with its date, start time, and minimum required headcount.

For a simple exact-match check, if `Coverage!A2` is the shift date and `Coverage!B2` is its start time, count matching assigned rows with:

`=COUNTIFS(Shifts!$A$2:$A$13,A2,Shifts!$D$2:$D$13,B2,Shifts!$H$2:$H$13,"Confirmed")`

Compare that count with the required headcount. This checks confirmed assignments for the specified date and start time; it does not detect overlapping intervals, missing meal relief, absences, or an employee assigned simultaneously to two sites. In the fictional schedule, mark each assignment Confirmed only after the worker accepts it. If all 12 are confirmed, the confirmed-window count is 12/12.

For real multi-site scheduling, add a conflict review for overlapping shifts and travel time. A worker cannot reliably cover two distant locations merely because the total weekly hours are below a threshold.

Keep the schedule realistic

Before publishing, review availability, agreed days off, site access, role qualifications, travel time, and the time needed to open or close a location. A 4:00 p.m. shift start may be unrealistic if the same worker finishes a different assignment at 4:00 p.m. across town.

Check employee qualifications separately from calendar availability. A person being free on Wednesday does not mean they are trained for a restricted task. VectoraWork's employee training matrix guide explains a separate competency-tracking process. Verify the live internal link before publishing this article.

Use consistent shift labels and a change log. If a supervisor replaces Ben with Cora, record the revision date, who approved it, and whether the replacement confirmed. Do not overwrite an old schedule without preserving the version sent to employees.

Share and protect the plan

Send the approved schedule through the team's agreed channel and make it clear which version is current. Avoid sharing personal contact details or sensitive site-access information in a public link. For an offline spreadsheet, store the master file in a controlled location and back it up; do not assume every employee's downloaded copy stays synchronized.

If you later use a browser-based offline scheduling app, check how it exports data and whether a restore actually works. A local app is not automatically a shared, multi-user scheduling system. Browse VectoraWork's small-business tools for related software, but verify individual product capabilities before promising scheduling, sync, or payroll features.

Common questions

Does an eight-hour shift always mean eight paid hours?

No. It depends on actual work and the applicable treatment of meal and rest periods. This fictional example assumes a qualifying 30-minute unpaid meal and therefore models 7.5 paid hours.

Can I use this for overnight staff?

The simple time formula handles ordinary overnight shifts shorter than 24 hours. Use full date-times and special review for daylight-saving changes, long shifts, or workweeks that split an overnight shift.

Does the spreadsheet calculate overtime pay?

No. It flags planned hours for review. Actual overtime depends on compensable hours worked, worker classification, the defined workweek, and applicable law.

Is there a downloadable workbook attached?

Not yet. This article provides the layout, formulas, and test data. Do not advertise a free Excel download until a working file has been created, uploaded, and checked.

Practical takeaway

Plan one row per shift, use genuine Excel dates and times, total hours by employee, and verify each required service window rather than trusting the weekly hour total alone. In the fictional Harborline schedule, 12 assignments provide 90 planned paid hours: 37.5 for Ava, 30 for Ben, and 22.5 for Cora. A good schedule is a confirmed staffing plan; actual attendance, payable hours, and safety requirements still need their own records.

Editorial disclosure: Harborline Service Crew, the client, the staffing requirements, and all assignments are fictional. This article provides general planning education, not payroll, employment-law, or professional compliance advice.