Build an Employee Shift Schedule and Hours Tracker with Overtime in Excel

You get a ready-to-build spreadsheet layout with formulas for overnight shifts, unpaid breaks, weekly hours per employee and highlighted overtime.

Prompt

Prompt
You are a spreadsheet designer who builds payroll-ready time tracking sheets for small businesses.

Your job: design an employee shift schedule and hours tracker that calculates paid hours, weekly totals per person and overtime.

Before building, ask me these questions in one message and wait for my answers:
1. Excel or Google Sheets?
2. How many employees, and which day does your work week start?
3. When does overtime start (for example, over 40 hours a week or over 8 hours a day)?
4. How long is the unpaid break, and after how many shift hours does it apply?

Rules:
- Overnight shifts must calculate correctly (use MOD on end minus start, never a plain subtraction).
- Put every policy number (overtime threshold, break length, break trigger) in a Settings sheet; formulas reference those cells, no hardcoded numbers.
- Paid hours show as decimals rounded to 2 places, ready for payroll.
- Weekly totals use SUMIFS by employee and week start date.
- Overtime rows are highlighted with conditional formatting.

Output format:
1. Sheet layout: one table per sheet listing column letter, header and what goes in it.
2. Formulas: each formula with its exact cell, plus a one-line plain explanation.
3. Conditional formatting rules: range, formula, format.
4. Test check: 3 sample rows including one overnight shift, with expected results.

End with one line noting that overtime and break rules vary by location, this is not legal advice, and to confirm with local labor rules or an employment professional.

Example output

Example for: a café with 6 staff using Excel, week starts Monday, overtime over 40 hours a week, 30-minute unpaid break on shifts over 6 hours.

1. Sheet layout

Settings

CellLabelValue
B1Weekly overtime threshold (hours)40
B2Unpaid break (minutes)30
B3Break applies to shifts over (hours)6

Shifts

ColumnHeaderContent
ADateShift date
BEmployeeName from a dropdown list
CStartTime, e.g. 22:00
DEndTime, e.g. 06:00
EBreak (min)Formula
FPaid HoursFormula
GWeek StartFormula

Weekly Summary

ColumnHeader
AEmployee
BWeek Start
CTotal Hours
DRegular Hours
EOvertime Hours

2. Formulas

  • Shifts!E2: =IF(MOD(D2-C2,1)*24>Settings!$B$3,Settings!$B$2,0) gives a break only when the shift is longer than 6 hours.
  • Shifts!F2: =ROUND((MOD(D2-C2,1)-E2/1440)*24,2) handles shifts that cross midnight and subtracts the break.
  • Shifts!G2: =A2-WEEKDAY(A2,3) returns the Monday of that week.
  • Summary!C2: =SUMIFS(Shifts!F:F,Shifts!B:B,A2,Shifts!G:G,B2) totals each person's week.
  • Summary!D2: =MIN(C2,Settings!$B$1) caps regular hours at 40.
  • Summary!E2: =MAX(0,C2-Settings!$B$1) shows hours above 40.

Format Shifts!G and Summary!B as dates, Shifts!C:D as hh:mm.

3. Conditional formatting

RangeFormulaFormat
Summary!A2:E100=$E2>0Light red fill, bold
Summary!A2:E100=AND($C2>=36,$E2=0)Yellow fill (close to overtime)

4. Test check

EmployeeStartEndBreakPaid Hours
Maya Ortiz22:0006:00307.50
Leo Brandt09:0017:30308.00
Priya Shah12:0016:0004.00

If Maya works five 7.5-hour nights plus one 4-hour shift, Summary shows 41.5 total, 40 regular, 1.5 overtime, highlighted red.

Overtime and break rules differ by state and country; this is not legal advice, so confirm daily overtime or paid-break requirements with your local labor office or an employment professional.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Requiring MOD for end minus start fixes the most common timesheet bug: overnight shifts showing negative hours.
  • A Settings sheet means you change the overtime threshold or break once instead of editing every formula.
  • The test rows with expected results let you confirm the sheet works before using it for payroll.

Use when you schedule hourly staff and need overtime totals for payroll. Have your overtime rule and break policy ready. Works in ChatGPT, Claude or Gemini for Excel or Google Sheets.

Common edits

  • Add a daily overtime column for places that pay overtime after 8 hours in a day.
  • Ask for an hourly rate column and gross pay with a 1.5x overtime multiplier.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets