What the Excel versions work out
Type the clock times into the shaded cells and the workbook does the rest. Nothing is hidden in a macro and no cell is locked, so you can open any formula up and see what it does — or change it if your workplace counts hours differently.
| You enter | The sheet works out |
|---|---|
| Time in, time out, unpaid break in minutes | Hours worked that day, to two decimal places |
| — | Total hours for the week or period |
| — | Regular and overtime hours, split at 40 per workweek |
| Your hourly rate and overtime multiplier | Regular pay, overtime pay and gross pay |
Shifts that run past midnight
A shift clocking in at 10:00 PM and out at 6:00 AM is eight hours, but subtracting one time from the other gives minus sixteen. The sheets wrap the subtraction around the day boundary, so overnight shifts come out right without you having to split them across two rows.
Daily hours are not rounded before they are added
The Hours column shows two decimal places, but the value underneath is exact. The weekly total adds those exact values and rounds once, at the end. This is the same rule as the by-hand advice further down: three days of seven hours fifty minutes is 23.50 hours, not the 23.49 you get by rounding each day to 7.83 first. On a full month that difference compounds into real money.
Why two of the sheets do not calculate overtime for you
The weekly and biweekly workbooks split overtime automatically, because their periods line up with whole workweeks. The semi-monthly and monthly ones deliberately do not.
Overtime is owed on hours over 40 in each workweek. A semi-monthly period is pinned to calendar dates, so it starts and ends in the middle of a week — and the rest of that week's hours are sitting on the adjoining sheet. Any formula that split overtime inside one of those sheets would be guessing. Both workbooks give you an overtime cell to fill in once you have totalled the split week across both periods, and a note saying so.
Opening them
They are ordinary .xlsx workbooks with no macros. Every formula uses basic functions that Excel, Google Sheets, LibreOffice Calc and Numbers all support. To use one in Google Sheets, upload the file to Drive and open it with Sheets.
Which one to use
Match the template to how you actually get paid, not to how you prefer to record time. If the sheet's periods do not line up with the payroll run, someone has to reconcile the difference by hand every cycle.
| If you are paid | Times a year | Use |
|---|---|---|
| Every week | 52 | Weekly |
| Every two weeks, same weekday | 26 | Biweekly |
| Twice a month on fixed dates | 24 | Semi-monthly |
| Once a month | 12 | Monthly |
Biweekly and semi-monthly are the two people mix up, and it matters here because the sheets are laid out differently. Biweekly is every 14 days and always contains exactly two whole workweeks. Semi-monthly is pinned to calendar dates, so its periods are 13 to 16 days long and cut through the middle of a week. The pay periods calculator works out which one you are on and lists every date.
Why the weeks are kept separate
The biweekly sheet totals each week on its own rather than giving you one 80-hour figure, and that is deliberate.
Overtime follows the workweek, not the pay period
Under the Fair Labor Standards Act, overtime is owed for hours over 40 in each individual workweek — a fixed, recurring 168-hour period. The pay period has nothing to do with it.
Someone works 50 hours one week and 30 the next. Eighty hours across the period, so no overtime? No. That is ten overtime hours in the first week, and the short second week does not offset it. A sheet that only shows the combined total makes that mistake easy to miss.
The same problem is worse on semi-monthly, where a pay period genuinely splits a workweek in half. If you are tracking hourly staff on a semi-monthly cycle, the hours in the split week have to be totalled across both sheets before overtime can be worked out.
Filling one in by hand
If you are using the PDFs rather than the workbooks, these four habits prevent most of the errors that turn up on paper sheets.
- Write times in whichever format your workplace uses, but be consistent. If people mix 5:30 and 17:30 on the same sheet, someone will misread one.
- Record the unpaid break in minutes, not as a time range. "30" is unambiguous; "12:00–12:30" invites double-counting.
- Convert minutes to decimals only at the end. Total the minutes first, then divide by 60 once. Converting each day and adding the rounded figures compounds the error.
- Never write minutes after the decimal point. Eight hours thirty minutes is 8.50, not 8.30 — 0.30 of an hour is 18 minutes, so that slip costs twelve minutes every time it happens.
The reminder line at the foot of every sheet covers the three values people look up most. The hours and minutes calculator has the full chart for all 59 minutes, in both directions.
Keeping the records
Employers are required to keep payroll records, and the retention periods are worth knowing before anyone throws out a stack of paper sheets. Under the FLSA, payroll records must be kept for at least three years. The records used to work out wages — including timesheets, work schedules, and records of additions to or deductions from wages — must be kept for at least two years.
The Act does not require any particular form for those records, and no specific timekeeping method is mandated. Paper sheets are entirely acceptable, provided the information they hold is complete and accurate.
What a record has to contain
For each non-exempt worker, the required information includes the hours worked each day and the total hours each workweek, along with the basis on which wages are paid, the regular hourly rate, straight-time and overtime earnings, and the dates the pay period covers.
Every template here has fields for all of it except the rate, which most employers hold separately rather than writing on each sheet.
Using these templates
They are free for any use, personal or commercial. Print them, photocopy them, hand them to staff, keep them in a binder, edit the workbooks to match your own payroll rules. No attribution is required for using a sheet, and no sign-up is needed to download one.
If you want to host the files yourself or put a calculator on your own site, the embed page covers that — the only condition there is keeping the attribution link.
Sources
- U.S. Department of Labor, Wage and Hour Division — Fact Sheet #21: Recordkeeping Requirements under the Fair Labor Standards Act. Required records, the three-year and two-year retention periods, and the absence of any required form or timekeeping method.
- U.S. Department of Labor, Wage and Hour Division — Handy Reference Guide to the Fair Labor Standards Act. The workweek definition and the 40-hour overtime threshold.
- 29 C.F.R. Part 516 — records to be kept by employers.