How to Calculate Overtime and Weekend Hours from a Timesheet
Turning a raw list of clock-in/clock-out timestamps into "straight time," "overtime," and "weekend hours" sounds simple until you actually try it in a spreadsheet. Here's how the split typically works, the thresholds involved, and the mistakes that most often throw the numbers off.
Straight time vs. overtime: the basic split
Most systems start with the same two buckets: straight time (hours worked up to a standard threshold) and overtime (hours worked beyond it, typically paid at a higher rate). The threshold and the multiplier are where the variation begins.
- In the US, the federal default under the Fair Labor Standards Act is overtime after 40 hours in a workweek, at 1.5× the regular rate — but several states (California among them) also apply daily overtime after 8 hours in a single day.
- Many other countries define overtime differently — some by daily thresholds, some by weekly, some with different multipliers for weekday vs. weekend overtime.
- Some employment contracts define their own thresholds entirely, independent of the statutory minimum.
Because of this variation, there's no single formula that's correct everywhere — the calculation always starts with knowing which threshold applies to the specific worker and jurisdiction.
Weekend and holiday premiums
On top of the straight time/overtime split, many pay structures add a separate premium for hours worked on weekends or public holidays — sometimes regardless of whether the weekly overtime threshold has been crossed. This means a given hour of work can potentially fall into more than one premium category at once (e.g., "weekend" and "overtime" simultaneously), and how those stack — additively or as separate line items — again depends on the specific policy in place.
Turning raw timestamps into these categories
Regardless of which thresholds apply, the underlying data problem is the same: you have a list of start and end timestamps, and you need to know how many of the total hours fall into each bucket. In practice that usually means computing, for each shift or pay period:
- Total hours — every hour worked, no exclusions.
- Weekend hours — hours that fall on whichever days count as the weekend for that worker or region.
- Business/weekday hours — the remainder, typically within a defined daily window.
- Then, separately, apply the relevant overtime threshold to the weekly or daily total to determine how much of it is straight time vs. overtime.
Steps 1–3 are exactly what our Bulk Spreadsheet Calculator automates: upload a timesheet with start/end timestamp columns and it appends Total Hours, Business Hours, Weekend Hours, and Weekday Hours for every row, with configurable weekend days and business-hours window. Step 4 (applying your specific overtime threshold) is a policy decision that depends on your jurisdiction, so it's worth applying on top of those totals rather than assuming a single universal rule.
Common mistakes
- Assuming Saturday/Sunday is the weekend for everyone. If any part of the workforce operates on a different week (see our guide to business hours by region), a hardcoded Saturday/Sunday rule will misclassify their hours.
- Not handling shifts that cross midnight. A shift that starts Friday night and ends Saturday morning needs to be split at the day boundary, or the weekend-hours count will be wrong.
- Forgetting holidays entirely. A holiday that falls on a normal weekday still needs to be excluded from "regular business hours" if your policy pays it differently.
- Double-counting or dropping overlapping premiums. When weekend and overtime premiums can both apply to the same hour, it's easy to either double-pay or miss one — worth checking explicitly rather than assuming the spreadsheet formula handles it.