Step 1: the layout

Put employees in rows and give each day two columns, start and end. Times typed as 7:00 or 15:30 are stored as fractions of a day, which is what makes the arithmetic work.

Column layout for Monday (repeat for each day)
ABCDE
EmployeeRoleMon startMon endMon hours
MayaOpener06:0014:008.00
SamOvernight22:0006:008.00
Column layout for Monday (repeat for each day)

Step 2: hours for one shift

In E2 type =IF(OR(C2="",D2=""),"",MOD(D2-C2,1)*24). MOD(end − start, 1) gives the length of the shift as a fraction of a day even when the end is the next morning, and × 24 turns it into hours. Sam's 22:00 to 06:00 gives 8.00 instead of a negative number.

Step 3: weekly total and overtime flag

  • Weekly hours: =SUM(E2,H2,K2,N2,Q2,T2,W2), adding each day's hours column.
  • Highlight 40 or more: select the total column, then Conditional formatting, rule "greater than or equal to" 40.
  • Team total at the bottom: =SUM(X2:X20) over the totals column.

Step 4: who is working at a given time

To count people working Monday at 12:30, put 12:30 in a cell such as Z1 and use =SUMPRODUCT((C2:C20<>"")*(MOD($Z$1-C2:C20,1)<MOD(D2:D20-C2:C20,1))). It counts every row whose shift contains that time, including shifts that cross midnight. Compare it with how many people you need then.

Step 5: printing and sharing

  1. Set the print area to the grid, landscape, fit to one page wide.
  2. Freeze the first two columns so names stay visible when you scroll.
  3. Share a view-only link in Google Sheets, or a PDF, so nobody edits the master by accident.
  4. Save each week as its own tab named by its Monday's date.

If you'd rather not build it, the weekly employee schedule template and the full-service restaurant template have these formulas already. When the spreadsheet starts costing more time than it saves, read Excel vs scheduling software.