How to Track Employee Hours in a Spreadsheet (and When to Stop)
A spreadsheet is a real way to track hours for a tiny crew, so this guide builds a working weekly timesheet with a weekly-40 overtime formula that matches time-and-a-half. Then it tells you the truth about the five places a spreadsheet gives out, so you know exactly when to stop.
JULY 11, 2026 · BY THE CLOX TEAM
A spreadsheet is a real answer to tracking employee hours, and for a very small crew it can be the right one for a while. If you're one or two people and you already live in Google Sheets or Excel, you don't need to buy anything to get organized this week. This guide sets up a working weekly timesheet, including an overtime formula that actually matches time-and-a-half after 40 hours, so you have something usable by the end of the day.
It also tells you the truth about where the spreadsheet stops holding up, because pretending it never does would waste your time. When the honest answer is a spreadsheet, we say so. When it's not, we'll show you exactly which crack it fell through.
Who this is for
A crew owner tracking a handful of people who wants a free, working timesheet today, and an honest read on how long it will last before it starts costing more than it saves.
01
The columns a weekly timesheet actually needs
Most homemade timesheets have too many columns and still miss the ones that matter. You don't need a column for everything. You need enough to reconstruct a day and to feed payroll. Here's the minimum set that holds up:
- Employee name, so one sheet can hold the whole crew or one tab per person
- Date, one row per person per day
- Clock in, the real start time
- Clock out, the real end time
- Break (minutes), unpaid break time to subtract
- Hours worked, a formula, never typed by hand
- Project or job site, so hours land against the right job
- Notes, for the day someone left early or a punch got fixed
The two rules that keep a timesheet trustworthy: never type the hours total by hand, and never leave the break blank when there was one. A typed total is a guess, and a missing break quietly overpays.
02
The daily-hours formula
Clock in and clock out should be real times, like 7:30 and 16:15. To turn two times plus a break into worked hours, subtract the times, multiply by 24 to convert the day-fraction into hours, then subtract the break in minutes over 60. If your columns are In in C, Out in D, and Break minutes in E, the formula in row 2 is =(D2-C2)*24 - E2/60.
That gives you decimal hours, so 8.75 means eight hours and forty-five minutes. Format the In and Out cells as time and the result cell as a plain number with two decimals. Decimal hours are what payroll wants anyway, so don't fight the sheet to show minutes.
The overnight-shift trap
If a shift crosses midnight, D2 minus C2 goes negative and the total is wrong. For crews that run overnight, add a plus one to the day, or split the shift into two rows at midnight. Most trades crews never hit this, but if yours does, know that the simple formula silently lies on those rows.
03
An overtime formula that matches time-and-a-half after 40
Under the federal Fair Labor Standards Act, most hourly employees earn 1.5 times their regular rate for hours worked past 40 in a workweek. So overtime is a weekly total, not a daily one. The move is to total the week first, then split it into regular hours capped at 40 and overtime hours for anything above.
If the week's total worked hours sits in cell H2, put regular hours in one cell as =MIN(H2, 40) and overtime in the next as =MAX(H2 - 40, 0). Then pay is regular hours at the base rate plus overtime hours at 1.5 times the base rate. With a base rate in cell B2, the whole thing collapses into one formula: =MIN(H2,40)*B2 + MAX(H2-40,0)*B2*1.5.
The MIN and MAX pair is what keeps the split honest. Below 40, overtime resolves to zero and everything pays at the base rate. Above 40, only the hours past 40 get the 1.5 multiplier. This matches federal weekly-40 overtime. It does not handle daily overtime or double-time, which some states require. California is the well-known one, where hours past eight in a single day earn 1.5 and it climbs from there.
If you are in a daily-overtime state
A weekly-40 formula will underpay a worker who does four ten-hour days and takes the rest of the week off. They hit 40 for the week, so weekly math sees no overtime, but a daily-overtime state owes them 1.5 for the hours past eight each day. A single sheet formula can't easily catch that. This is one of the first places the spreadsheet quietly gets payroll wrong.
04
A filled-in week, so you can copy the shape
Here's one worker's week using the formulas above, base rate $30 an hour, a 30-minute unpaid lunch each day. The Hours column is the daily formula; the totals row is where the overtime split happens.
One worker, one week. Hours are computed, never typed. The week totals 44.5, so 40 pay at the base rate and 4.5 at time-and-a-half.
At 44.5 hours: 40 at $30 is $1,200, plus 4.5 overtime hours at $45 is $202.50, for $1,402.50 gross that week. Because the hours came from a formula and the split came from MIN and MAX, you can trust the number without re-adding anything on a calculator.
Build it from the example above
Copy the column layout and the formulas from the filled-in week and you have a working weekly timesheet in a few minutes. It totals hours correctly and splits overtime for you. What it will never grow is a way to prove a shift actually happened, which is the ceiling every spreadsheet hits.
05
When to stop: the five places a spreadsheet gives out
None of this is the spreadsheet's fault. A sheet stores and computes numbers well. The problem is that a timesheet isn't really about numbers. It's about whether the numbers are true, and a spreadsheet has no way to know.
1. There is no proof a shift actually happened
A row that says 7:00 to 16:30 is just text somebody typed. It doesn't know if the person was on site, on the couch, or nowhere. For a crew you can see all day, that may be fine. The moment a worker is on a job site you're not standing on, the sheet can't tell you the shift was real, only that someone entered it.
2. It is trivial to backdate or edit after the fact
Anyone with the file can change 16:00 to 17:00 next Tuesday and nothing records that it happened. There's no history, no signature, no way to prove the Friday numbers you approved are the same ones that got paid. When a payroll dispute lands, a spreadsheet is word against word, which is exactly the situation you were trying to get out of.
3. There is no offline capture in the field
Times get remembered and typed in later, often on Sunday from memory. Memory rounds. 7:48 becomes 7:45 or 8:00, and every rounded punch is money moving the wrong way. A sheet can't capture a punch at the moment it happens on a job site with no signal, so it captures a reconstruction instead.
4. Overtime is manual, and manual means eventually wrong
The formula above is correct for weekly 40, but you still have to build it right, protect it from a stray edit, and remember the states and shifts it doesn't cover. Every hand-built formula is one fat-fingered cell away from silently underpaying or overpaying, and you won't notice until someone checks.
5. It breaks down past a few people
One tab per person, copied formulas, a summary tab that has to be rebuilt when someone joins or leaves, and a weekly ritual of chasing everyone for their times. It works at two or three. At eight or ten it becomes a part-time job, and the errors scale with the headcount.
What a spreadsheet does well, and the five things it structurally cannot do.
06
The line worth drawing
Use the spreadsheet while the honest answer is a spreadsheet: a tiny crew you can see, weekly-40 overtime, low stakes if a number is off by a few minutes. The day you can't personally vouch for where everyone was, or the day a payroll dispute turns into your word against theirs, the spreadsheet has run out of road. That's a capture-and-proof problem, and no formula fixes it.
This is where a purpose-built time clock does something a sheet cannot: it captures the punch at the moment it happens, records who changed what, and gives you a record you can actually stand behind. With a real time-tracking setup, a worker taps once to clock in, the punch saves on the phone with its true timestamp even with no signal, and syncs when signal returns. You're not retyping a week from memory on Sunday.
On the proof side, the difference is structural. A manager can still fix a genuine mistake, but when they edit a shift, the correction is visible and re-certified rather than silently overwritten, and the punch record itself is signed and hash-chained so it can't be altered after the fact without detection. That's about the integrity of the record after it's captured, not a claim that any single punch was in the right spot. Anyone can check a record on the public verify page.
An honest limit, stated plainly
A time clock captures a punch at the moment it happens and makes the record tamper-evident afterward. It's a strong deterrent, not a guarantee that a location was truthful, because location still comes from the worker's phone. It solves the spreadsheet's backdating and no-capture problems. It doesn't turn any tool into a lie detector.
If you have ever settled a disagreement about hours by trusting one side, the prove-hours problem is the real reason a spreadsheet eventually costs more than it saves. That's its own topic, covered in depth here.
If the spreadsheet is starting to feel like a second job, that's the signal to try the version where setup happens once instead of every Friday. Start a free 14-day trial. No credit card to start, and a 30-day money-back guarantee if it's not a fit. Keep your spreadsheet as a backup for the first couple of weeks and compare the two. If the app doesn't save you time, you'll know quickly, and you won't have lost anything.