Build an Overtime Report from Shift Data

Overtime is one of the larger costs a manager can actually influence, and one of the hardest to see clearly. The data comes from a clocking system or a timesheet export — clock in, clock out, sometimes a break — and turning it into paid overtime involves rules that a spreadsheet handles awkwardly: a night shift that starts on Friday and ends on Saturday, a thirty-minute unpaid break on shifts over six hours, time rounded to the nearest quarter hour, overtime counted against contracted hours for the week rather than the day, and different rates for Sundays and bank holidays. On top of the cost, there is a compliance question: in the UK, the Working Time Regulations limit average working time to 48 hours a week over a reference period unless an employee has opted out. This page builds the overtime report with pandas from clock records: paid hours per shift, weekly overtime above contract, premium hours, cost by department and a check of average weekly hours. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl holidays

Two inputs: staff with department, contracted weekly hours, hourly rate and whether they have opted out of the 48-hour limit; and clock records for twelve weeks from July to September 2026, one row per shift with clock-in and clock-out timestamps. Some transport shifts run overnight. The pay rules used here are typical rather than universal: a 30-minute unpaid break on shifts longer than six hours, time rounded to the nearest 15 minutes, overtime at time-and-a-half on hours above contract in a week, and double time for hours worked on Sundays and bank holidays.

Step 1 — Paid hours per shift

import pandas as pd

staff = pd.read_excel("staff.xlsx").set_index("Employee")
clock = pd.read_excel("clock_records.xlsx", parse_dates=["Clock in", "Clock out"])

clock["Worked h"] = (clock["Clock out"] - clock["Clock in"]).dt.total_seconds() / 3600
assert (clock["Worked h"] > 0).all() and (clock["Worked h"] < 16).all(), "implausible shift length"
clock["Break h"] = (clock["Worked h"] > 6) * 0.5
clock["Paid h"] = ((clock["Worked h"] - clock["Break h"]) * 4).round() / 4      # nearest quarter hour
clock["Shift date"] = clock["Clock in"].dt.normalize()
clock["Week"] = clock["Shift date"].dt.to_period("W-SUN")
print(clock[["Employee", "Clock in", "Clock out", "Worked h", "Paid h"]].head(4).round({"Worked h": 2}).to_string(index=False))
print("overnight shifts:", int((clock["Clock out"].dt.date != clock["Clock in"].dt.date).sum()))

Working time is clock-out minus clock-in, computed from full timestamps so that a shift from 22:07 on Friday to 06:40 on Saturday is simply eight and a half hours — the subtraction does not care that the date changed, which is exactly where formulas comparing times of day in Excel go wrong. A sanity check rejects zero, negative or implausibly long shifts, which almost always mean a missed clock-out. The unpaid break is deducted from shifts over six hours, and the result is rounded to the nearest quarter hour, as many payroll systems do. Each shift is assigned to the date and the Monday-to-Sunday week it started in, a common convention for overnight work; some agreements split the hours at midnight instead, which is a small change to this step.

Step 2 — Weekly overtime and premium hours

import holidays

bank = holidays.country_holidays("GB", subdiv="ENG", years=[2026])
clock["Premium day"] = (clock["Shift date"].dt.weekday == 6) | clock["Shift date"].dt.date.isin(bank)

weekly = clock.groupby(["Employee", "Week"]).agg(Paid=("Paid h", "sum"),
                                                  Premium=("Paid h", lambda s: s[clock.loc[s.index, "Premium day"]].sum()))
weekly = weekly.join(staff[["Contract hours", "Hourly rate"]], on="Employee")
weekly["Overtime h"] = (weekly["Paid"] - weekly["Contract hours"]).clip(lower=0)
weekly["Double-time h"] = weekly[["Premium", "Overtime h"]].min(axis=1)       # premium-day hours within overtime
weekly["Time-and-a-half h"] = weekly["Overtime h"] - weekly["Double-time h"]
weekly["Overtime cost"] = (weekly["Time-and-a-half h"] * 1.5 + weekly["Double-time h"] * 2.0) * weekly["Hourly rate"]
print(weekly.groupby("Employee")[["Paid", "Overtime h", "Double-time h", "Overtime cost"]].sum().round(1).to_string())

Overtime here is measured per week: paid hours above the employee's contracted weekly hours, so a long Tuesday balanced by a short Thursday is not overtime. Within the overtime, hours worked on a Sunday or a bank holiday — the 31 August bank holiday falls in this period — are paid at double time and the rest at time and a half; counting premium-day hours within the overtime avoids paying the same hour twice. The summary per employee already shows the pattern managers need to see: two people, one in the warehouse and one in transport, account for most of the overtime, regularly working long weeks including weekends. Cai in the warehouse worked 211.5 overtime hours over the twelve weeks and Dee in transport 247, between them about 80% of the £14,159 total overtime cost, while the office worked almost none.

Step 3 — Cost by department and the 48-hour check

dept = weekly.join(staff["Department"], on="Employee").groupby("Department").agg(
    paid=("Paid", "sum"), overtime=("Overtime h", "sum"), cost=("Overtime cost", "sum"))
dept["Overtime share"] = (dept["overtime"] / dept["paid"] * 100).round(1)
print(dept.round(0).to_string())

avg = weekly.groupby("Employee")["Paid"].sum() / clock["Week"].nunique()
limit = pd.DataFrame({"Average weekly hours": avg.round(1), "Opted out": staff["48h opt-out"]})
limit["Over 48h average"] = (limit["Average weekly hours"] > 48) & ~limit["Opted out"]
print(limit.to_string())

Rolling the weekly figures up by department gives overtime hours as a share of all paid hours and the overtime cost — the figures a budget review uses. The compliance check averages each person's weekly hours over the period, counting weeks without shifts as zero, and flags anyone above 48 hours a week who has not opted out. The UK regulations average over a 17-week reference period by default, so a twelve-week average is an early warning rather than a determination; extending the export to 17 weeks makes it the real test. Here the transport driver working the most overtime averages over 48 hours without an opt-out, which is a conversation for the manager now, before the reference period closes and while there is still time to rebalance the rota. The warehouse employee who has opted out is shown but not flagged, since the opt-out is recorded in the staff sheet.

Overtime is often a symptom rather than a choice. Two people working sixty-hour weeks for a quarter usually means a vacancy that has not been filled, a rota with a gap at weekends, or a single person who holds knowledge nobody else has — and each of those has a better fix than premium pay. The overtime share by department, 23% of paid hours in transport against 17% in the warehouse and almost nothing in the office, shows where to look first. Long hours sustained for months are also a fatigue risk, particularly for drivers, which is a further reason the 48-hour check belongs in the same report as the cost rather than in a separate HR process.

Step 4 — The report

with pd.ExcelWriter("overtime_2026-Q3.xlsx") as xw:
    dept.to_excel(xw, sheet_name="By department")
    weekly.reset_index().assign(Week=lambda d: d["Week"].astype(str)).to_excel(xw, sheet_name="By person and week", index=False)
    limit.to_excel(xw, sheet_name="48-hour check")
    clock.assign(Week=clock["Week"].astype(str)).to_excel(xw, sheet_name="Shifts", index=False)
print("total overtime cost:", round(weekly["Overtime cost"].sum(), 2))

The workbook gives payroll the weekly overtime and premium hours per person, managers the departmental view and the 48-hour check, and everyone the underlying shifts with the paid-hours calculation visible. When overtime is concentrated in a few people week after week, it is usually cheaper to add capacity — a part-time role, a shift change — than to keep paying premium rates, and the report gives the numbers for that decision. The weekly hours also feed the rota planning in Create a Shift Calendar with Rotating Patterns.

Common pitfalls

• Comparing times of day. Use full timestamps so overnight shifts compute correctly.
• Overtime per day when the rule is per week. Sum the week first, then compare with contract.
• Paying premium hours twice. Count premium-day hours within the week's overtime.
• Ignoring missed clock-outs. Reject implausible shift lengths before calculating.
• Treating a short window as the legal test. Use the full reference period for the 48-hour average.

Conclusion

An overtime report computes each shift's paid hours from full clock-in and clock-out timestamps, deducts unpaid breaks and rounds to the quarter hour, then sums by employee and week, treats hours above contract as overtime and pays Sunday and bank holiday overtime at the higher rate. Rolled up by department it gives overtime share and cost; averaged per person it flags anyone above 48 hours a week without an opt-out, early enough to act.

Frequently asked questions

How do I calculate hours for a shift that crosses midnight in pandas? Subtract full timestamps — clock out minus clock in — rather than times of day; the date change is handled automatically.

How do I calculate weekly overtime from shifts? Sum paid hours per employee per week and subtract the contracted weekly hours, keeping only positive values.

How do I round worked time to the nearest quarter hour? Multiply hours by four, round, and divide by four.

What is the 48-hour average check? Under the UK Working Time Regulations, average weekly working time over the reference period, normally 17 weeks, should not exceed 48 hours unless the worker has opted out.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Create a Shift Calendar with Rotating Patterns — planning the shifts.
• Reconcile Payroll to the General Ledger — where overtime is paid.
• Build a Staff Utilisation Report from Timesheets — hours from another angle.