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.
| A | B | C | D | E |
|---|---|---|---|---|
| Employee | Role | Mon start | Mon end | Mon hours |
| Maya | Opener | 06:00 | 14:00 | 8.00 |
| Sam | Overnight | 22:00 | 06:00 | 8.00 |
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
- Set the print area to the grid, landscape, fit to one page wide.
- Freeze the first two columns so names stay visible when you scroll.
- Share a view-only link in Google Sheets, or a PDF, so nobody edits the master by accident.
- 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.