How is the overtime cost calculated?

Each overtime hour costs the base rate times its multiplier times one plus your pay-linked on-costs. Six hours at time and a half on a 28 per hour base rate with 10 percent on-costs is 6 x 28 x 1.5 x 1.10 = 277.20, of which the premium over straight time is 6 x 28 x 0.5 x 1.10 = 92.40. It is an estimate from base rates, not a payroll figure.

How do I size an overtime request before approving it?

On OT Request, divide the good units to recover by what the line has actually made per hour and add an allowance for setups and delays. To recover 2,400 cases on a line that has shown 600 good cases an hour with a 10 percent allowance takes 4.4 line-hours; with a crew of 7 that is 30.8 person-hours, priced at your rate and multiplier.

What overtime limits should I set?

Set the limits your law, collective agreement or fatigue policy requires; the workbook does not assume any. Rules differ widely, for example weekly overtime thresholds in the US and Canadian provinces, daily and weekly limits under India's Factories Act, and the 48-hour average in the EU Working Time Directive. The Limits column flags any week with overtime or total hours above the two limits you type.

Why should weeks with no overtime be logged too?

Because the overtime share needs all hours worked. A department with 160 regular hours and 20 overtime hours has an 11.1 percent overtime share; leave out the people who worked no overtime and the share looks higher than it is. One row per person per week also lets the Check column catch duplicate person-weeks.

How is the overtime budget compared?

Each department has a weekly overtime budget in hours on Setup. For any filter, the Dashboard multiplies the weekly budgets of the departments in view by the number of weeks in view, so a 30 hour weekly budget over 4 weeks is 120 hours, and shows the hours used, the share of budget used and a status in words.

Does it handle double time and holidays?

Yes. Overtime at rate 2 has its own column and multiplier on Setup, for example 2.0 for rest days or holidays worked. Paid leave and holidays not worked are not hours worked, so keep them out of regular hours; a 4-day holiday week simply has 32 regular hours.

Operations and Labor

Overtime Tracker Excel Template

Log each person's weekly hours and overtime, and see overtime hours, share, cost and reasons against department budgets and your weekly limits on a live dashboard.

Overview

This overtime tracker is a working Excel workbook for plant managers, supervisors and payroll or finance partners who need to see how much overtime is being worked, what it costs, why it happens and who is carrying it. It ships with eight weeks of example data for a fictional food plant with 16 people in five departments, so the dashboard and charts are populated the moment you open it; replace the example rows with your own and everything recalculates.

Each row is one person-week: regular hours plus overtime hours at two multipliers you set on Setup, such as time and a half and double time, with the main reason. The workbook adds the overtime share of hours worked, an estimated cost and the premium over straight time, using the person's base rate and a pay-linked on-cost percentage, the same cost model as the Overtime Cost Calculator on this site. A Limits column flags anyone above your weekly overtime or total-hours limit, and the Dashboard compares overtime with each department's weekly budget for any range of weeks.

Before approving more overtime, the OT Request sheet sizes it the way the Required Line Overtime Calculator does: good units to recover divided by the output the line has demonstrated, plus a setup and delay allowance, gives the line-hours; times the crew gives person-hours and the estimated cost. The rates and limits are yours to set from your own law, agreement and policy; the workbook does not decide what counts as overtime.

What this template includes

Overtime Log table: one row per person per week with regular hours, overtime at two rates you set, the reason and who approved it
Per-row overtime hours, total hours, overtime share, estimated cost and premium over straight time, and a Limits status in words against your weekly overtime and total-hours limits
Setup lists of departments with weekly overtime budgets, employees with base rates, and overtime reasons, plus your rates, on-costs and limits
Check columns that name problems in words: employee not on the list, week not starting on a Monday, regular hours above the normal week, overtime with no reason, duplicate person-weeks
OT Request sheet that sizes a request before approval: units to recover and the demonstrated line rate give line-hours, person-hours, cost and premium
Dashboard with department and week filters, KPI tiles for overtime hours against budget, overtime share against your ceiling, cost and person-weeks out of limits
Two native charts: overtime hours by week against budget, and a Pareto of overtime hours by reason
Weekly, by-department and by-person tables, a Method sheet with every formula and a Verify sheet that re-checks them (Self-check: PASS)

Suggested use case

Use it to review overtime weekly by department against budget, to spot people working past your fatigue limits, to find the reasons that drive overtime, and to size and price an overtime request before you approve it.

How to use it

On Setup, enter your currency, overtime multipliers, pay-linked on-costs, weekly limits and overtime share ceiling, then replace the example departments, employees and reasons.
On Overtime Log, add one row per person per week with regular hours, overtime hours at each rate, the reason and the approver.
Read the Limits and Check columns and fix any data problem they name.
Open the Dashboard and filter by department or weeks for your weekly review.
When someone asks for more overtime, fill in OT Request: good units to recover, the demonstrated output per hour, allowance, crew and base rate.

Frequently Asked Questions

How is the overtime cost calculated?: Each overtime hour costs the base rate times its multiplier times one plus your pay-linked on-costs. Six hours at time and a half on a 28 per hour base rate with 10 percent on-costs is 6 x 28 x 1.5 x 1.10 = 277.20, of which the premium over straight time is 6 x 28 x 0.5 x 1.10 = 92.40. It is an estimate from base rates, not a payroll figure.
How do I size an overtime request before approving it?: On OT Request, divide the good units to recover by what the line has actually made per hour and add an allowance for setups and delays. To recover 2,400 cases on a line that has shown 600 good cases an hour with a 10 percent allowance takes 4.4 line-hours; with a crew of 7 that is 30.8 person-hours, priced at your rate and multiplier.
What overtime limits should I set?: Set the limits your law, collective agreement or fatigue policy requires; the workbook does not assume any. Rules differ widely, for example weekly overtime thresholds in the US and Canadian provinces, daily and weekly limits under India's Factories Act, and the 48-hour average in the EU Working Time Directive. The Limits column flags any week with overtime or total hours above the two limits you type.
Why should weeks with no overtime be logged too?: Because the overtime share needs all hours worked. A department with 160 regular hours and 20 overtime hours has an 11.1 percent overtime share; leave out the people who worked no overtime and the share looks higher than it is. One row per person per week also lets the Check column catch duplicate person-weeks.
How is the overtime budget compared?: Each department has a weekly overtime budget in hours on Setup. For any filter, the Dashboard multiplies the weekly budgets of the departments in view by the number of weeks in view, so a 30 hour weekly budget over 4 weeks is 120 hours, and shows the hours used, the share of budget used and a status in words.
Does it handle double time and holidays?: Yes. Overtime at rate 2 has its own column and multiplier on Setup, for example 2.0 for rest days or holidays worked. Paid leave and holidays not worked are not hours worked, so keep them out of regular hours; a 4-day holiday week simply has 32 regular hours.