Back to blog
Jobs, stock and customers

Overtime tracker: a spreadsheet that flags overtime before payroll

A free overtime tracker for Excel and Google Sheets: totals each person's week, flags who will pass 40 hours before payday, and works out time-and-a-half pay.

Start from the timesheet template

Your crew clocks in from their phones.

Use the template, free
On this page

An overtime tracker is a sheet that adds up each person’s hours for the workweek and shows the hours over 40, which are paid at 1.5 times the regular rate. The useful ones also flag who will pass 40 before the week ends, so you can change Friday’s schedule before payroll.

It’s for the owner or office manager of a restaurant, a landscaping or other seasonal crew, a shop or a warehouse with 5 to 40 hourly people. Below: a free Excel tracker, the federal rule, states with daily overtime, bonuses, and when an app pays off. In California or another state with daily overtime? The sheet counts hours past a daily line and double time, but not every state rule; see the states section. If your hours already live in a spreadsheet, spreadsheet to app turns the workbook into an app with its own database, no account needed to try it. Prefer a ready-made app? Start from the timesheet template.

How we researched this: November 2026, from the Department of Labor, state labor agencies and each vendor’s own pricing page. Not legal or payroll advice.

What is the federal overtime rule, and what counts as a workweek?

The Fair Labor Standards Act (FLSA) says covered employees who aren’t exempt must be paid overtime for hours worked over 40 in a workweek, at no less than 1.5 times their regular rate. The Department of Labor’s overtime fact sheet is the page to read.

QuestionAnswer
What starts overtime?Hours worked over 40 in one workweek
How much is it paid?At least 1.5 times the regular rate
What’s a workweek?A fixed, repeating 168 hours, starting any day or hour
Can you average two weeks?No. A 50-hour week is overtime even if the next is 30
Does the pay period matter?No. A biweekly check still counts each week alone
Does drive time count?Between job sites, yes. Home to the first site, no (DOL)

Pick the day and hour your workweek starts, such as Monday at 12:00 a.m. or Sunday at 4:00 a.m. for a night crew, and keep it fixed. Someone paid $20 an hour who works 45 hours earns 40 × $20 = $800 plus 5 × $30 = $150, so $950.

Most hourly employees are owed overtime. Exemption depends on duties as well as pay: the Department of Labor says job titles don’t decide it, and the salary level is generally at least $684 a week, or $35,568 a year. For a salaried foreman or manager, ask your state labor department or an employment lawyer.

What goes in an overtime tracker spreadsheet?

One row per person per week, on a sheet named Week. You type columns A to K and formulas fill L to V. A second sheet, Settings, holds the rules in one place.

ColumnWhat goes in it
A NameThe person
B Hourly rateTyped, such as 18.00
C to I Mon to SunHours worked that day, 0 for a day off
J Still scheduledHours still to work this week
K BonusA bonus earned this week, if any
L Total hoursFormula: the week’s hours
M Daily overtimeFormula: hours past a daily line, if you set one
N Overtime hoursFormula: hours paid at 1.5 times
O Double time hoursFormula: hours past a daily double time line
P Regular hoursFormula: total less overtime
Q Hours until overtimeFormula: what’s left before 40
R Projected totalFormula: hours so far plus still scheduled
S StatusFormula: Over, Will go over, Near or OK
T Regular rateFormula: hourly rate once a bonus is folded in
U Extra pay for overtimeFormula: the extra half time, or extra double
V Pay for the weekFormula: before taxes

Settings has four numbers. B2 is where overtime starts (40). B3 is how close counts as Near (4). B4 is a daily overtime line and B5 a daily double time line, both left at 0 unless your state has one. The names and numbers in the sample are an example.

Dev, in the board above, is the reason for column J. At 38 hours on Thursday he looks fine until you add Friday’s shift and see 6 overtime hours coming. You’d see it on Thursday, not on payroll day.

Which formulas total the week and flag overtime?

Each formula is for row 2, then filled down. Each starts with a check so a blank row stays blank, and each uses functions found in both Excel and Google Sheets. Don’t want to type them? Open the box and paste.

Paste it in one go

Make a sheet named Settings, click A1 and paste the first block. Then make a sheet named Week, click A1 and paste the second block: the headings and Maria’s row, with every formula. Fill L to V down for each person.

Setting	Value	What it does
Overtime starts after (hours a week)	40	40 is the federal rule
Warn when someone is within (hours)	4	Status shows Near this close to 40
Daily overtime starts after (hours, 0 for none)	0	Leave at 0 for the federal rule
Double time starts after (hours a day, 0 for none)	0	Leave at 0 unless your state pays double time by the day
Name	Hourly rate	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Still scheduled	Bonus	Total hours	Daily overtime	Overtime hours	Double time hours	Regular hours	Hours until overtime	Projected total	Status	Regular rate	Extra pay for overtime	Pay for the week
Maria Lopez	18	8	8	8	8				8		=IF(COUNT(C2:I2)=0,"",SUM(C2:I2))	=IF(L2="","",IF(Settings!$B$4>0,SUMPRODUCT((C2:I2>Settings!$B$4)*(C2:I2-Settings!$B$4))-O2,0))	=IF(L2="","",M2+MAX(0,L2-M2-O2-Settings!$B$2))	=IF(L2="","",IF(Settings!$B$5>0,SUMPRODUCT((C2:I2>Settings!$B$5)*(C2:I2-Settings!$B$5)),0))	=IF(L2="","",L2-N2-O2)	=IF(L2="","",MAX(0,Settings!$B$2-L2))	=IF(L2="","",L2+N(J2))	=IF(L2="","",IF(N2+O2>0,"Over",IF(R2>Settings!$B$2,"Will go over",IF(L2>=Settings!$B$2-Settings!$B$3,"Near","OK"))))	=IF(OR(L2="",B2=""),"",IF(L2=0,B2,(B2*L2+N(K2))/L2))	=IF(T2="","",ROUND(T2*(0.5*N2+O2),2))	=IF(T2="","",ROUND(B2*L2+N(K2)+U2,2))

The week’s total adds the seven days. COUNT keeps an empty row empty:

L2: =IF(COUNT(C2:I2)=0,"",SUM(C2:I2))

Overtime hours (N) are hours over a daily line, if you set one, plus hours over 40 that weren’t already counted. With B4 and B5 at 0, M and O are 0 and N is just the hours over 40:

M2: =IF(L2="","",IF(Settings!$B$4>0,SUMPRODUCT((C2:I2>Settings!$B$4)*(C2:I2-Settings!$B$4))-O2,0))
N2: =IF(L2="","",M2+MAX(0,L2-M2-O2-Settings!$B$2))
O2: =IF(L2="","",IF(Settings!$B$5>0,SUMPRODUCT((C2:I2>Settings!$B$5)*(C2:I2-Settings!$B$5)),0))

Regular hours, hours until overtime and the projected total are one line each:

P2: =IF(L2="","",L2-N2-O2)
Q2: =IF(L2="","",MAX(0,Settings!$B$2-L2))
R2: =IF(L2="","",L2+N(J2))

Status is the flag. Over means overtime or double time hours are already in the sheet, whether they came from the week or from a long day. Next, Will go over: the hours so far plus what’s still scheduled pass 40. Then Near, within the warning distance of 40. Otherwise OK. Will go over looks only at the week, not at a long shift you haven’t entered yet:

S2: =IF(L2="","",IF(N2+O2>0,"Over",IF(R2>Settings!$B$2,"Will go over",IF(L2>=Settings!$B$2-Settings!$B$3,"Near","OK"))))

Color the Status column with conditional formatting: red for Over, amber for Will go over, yellow for Near. The pay formulas are in the section on bonuses.

Which states count overtime by the day?

Federal law looks only at the week, but some states add a daily line. This sheet handles a daily line and a daily double time line. It does not work out California’s seventh-consecutive-day rule, Colorado’s 12 consecutive hours, meal or rest break pay, tips, or two pay rates in one week. If your state has any of these, check the numbers with your payroll provider. Four examples from state pages, not a survey:

StateWhen overtime starts
CaliforniaOver 8 hours a day (double over 12), 7th straight day, or 40 a week (DIR)
ColoradoOver 12 hours a day or 12 in a row, or 40 a week (CDLE)
AlaskaOver 8 hours a day or 40 a week, but not for employers with under 4 employees (Alaska DOLWD)
TexasWeekly rule: over 40 hours in a seven-day week (TWC)

Some cities have rules too. For California, type 8 in Settings B4 and 12 in B5. For Alaska, type 8 in B4. For Colorado, type 12 in B4. Texas and states without a daily rule keep both at 0.

An example, set to California: Luis, a cook at $24 an hour, works 7 hours Tuesday to Friday and 10 on Saturday. That’s 38 hours, so no overtime under the federal rule. Under California’s, Saturday’s two hours past 8 are overtime: M is 2 and N is 2, Status shows Over, extra pay is 0.5 × $24 × 2 = $24, and pay for the week is 38 × $24 + $24 = $936.

Now a 13-hour Saturday after four 8-hour days, for another cook at $24: 45 hours. Hours 9 to 12 are overtime (N is 4), the 13th is double time (O is 1), and the other 40 are regular. Extra pay is 0.5 × $24 × 4 + $24 × 1 = $72, so pay is 45 × $24 + $72 = $1,152. Each overtime hour is counted once.

How do you handle bonuses in the overtime rate?

A bonus tied to the work can raise the overtime rate. The Department of Labor says nondiscretionary bonuses are included in the regular rate, meaning your hourly rate once the bonus is folded in. Production, quality, attendance and safety bonuses are examples. A truly discretionary bonus, where the employer decides whether to pay it and how much near the end of the period, with no earlier promise, stays out, and a label doesn’t decide it. The method: add the bonus to the week’s straight-time pay, divide by all hours worked, then pay half that rate for each overtime hour.

Marcus Hill earns $21 an hour, worked 41.5 hours and earned a $50 safety bonus this week:

  1. Straight time: 41.5 × $21 = $871.50.
  2. Add the bonus: $871.50 + $50 = $921.50.
  3. Regular rate: $921.50 ÷ 41.5 = $22.20.
  4. Extra half time: 0.5 × $22.20 × 1.5 overtime hours = $16.65.
  5. Pay for the week: $921.50 + $16.65 = $938.15.

Without the bonus, the extra pay would be $15.75, so the bonus adds $0.90. The Department of Labor works a $10 an hour example with a $50 bonus on the same page, and the steps are the same. The sheet does this in three columns:

T2: =IF(OR(L2="",B2=""),"",IF(L2=0,B2,(B2*L2+N(K2))/L2))
U2: =IF(T2="","",ROUND(T2*(0.5*N2+O2),2))
V2: =IF(T2="","",ROUND(B2*L2+N(K2)+U2,2))

This is for a bonus earned in one week and a person paid one rate. A bonus earned over a month or a quarter, or hours at two rates, needs a different calculation, so ask your payroll provider or the Wage and Hour Division.

How should you round time, and what records must you keep?

Rounding is allowed if it works both ways. The federal rule says start and stop times can be recorded to the nearest 5 minutes, or the nearest tenth or quarter of an hour, as long as it doesn’t, over time, shortchange people on the hours they worked. Always rounding down would. Section 785.48 of 29 CFR has the wording.

The Department of Labor’s recordkeeping page lists what to keep for nonexempt employees: hours worked each day, total hours each workweek, the day and time the workweek begins, the regular hourly rate and total overtime earnings for the week. Keep payroll records for at least three years, and the records behind the math, like time cards, for two. Your state may ask for more.

Before each payroll run, print the weekly sheet (the PDF has this list) and check:

  • ☐ Every person’s days are filled in, with 0 for days off.
  • ☐ Totals cover the whole workweek, not the pay period.
  • ☐ Anyone over 40 hours has overtime hours written down.
  • ☐ In a state with daily overtime, long days are checked against the daily limit.
  • ☐ Bonuses are added to the week they were earned.
  • ☐ Anyone at 36 hours or more on Thursday is checked against Friday’s schedule.

Each week, right-click the Week tab, choose Move or Copy, tick Create a copy and name it with the week’s start date. Clear the hours in the copy. One tab per week is your record.

When should you move overtime tracking out of a spreadsheet?

A spreadsheet works while you type hours yourself from timecards. It stops working when:

  • Several people edit the sheet and someone types over a formula.
  • Hours arrive on Monday, after the week has closed.
  • You need a clock, not handwriting and guesses at breaks.
  • Hours change after the fact and you can’t say who changed them or when.

Tracking time off too? See our PTO tracker and time off request form. Prices below are from each vendor’s pricing page in November 2026.

ToolWhat it costs, as listed
Homebase$0 for one location, up to 10 employees. Plus is $70 a location a month.
ClockifyStandard is $5.49 per user a month billed yearly, $6.99 monthly.
This spreadsheet$0
  • Homebase suits an hourly team at one location. Its time clock page says Plus also alerts you when employees approach or reach overtime. Its pricing page has a monthly and annual toggle with a 20% saving badge, so check which rate the $70 shows. Plus isn’t charged per person.
  • Clockify suits a smaller crew that wants hours and an overtime report. Its pricing page puts attendance and overtime reports, approvals and time off in Standard and above, and has a free plan for a small team (the card says up to 5 users, the FAQ says 4 invites). Each active or invited user is a paid seat.

At 15 people, Clockify Standard is 15 × $5.49 = $82.35 a month billed yearly, about $988 a year, or $104.85 billed monthly. Homebase Plus is $70 a month for the location. At the listed prices, Homebase costs less from about 13 people up, if the $70 is the rate you’d pay. If you already run payroll, ask your provider whether it flags people approaching overtime.

Which would we look at first? For 13 or more hourly people at one location, Homebase, because the clock and the overtime alerts sit together. For a smaller crew, Clockify. If your only problem is Friday’s surprise, the spreadsheet above is enough.

Can GoodTaco track hours and flag overtime?

Dev’s Thursday is what the timesheet template is for. People clock in and out against a job, switching jobs as the day moves, from a phone, or a manager clocks them in. The Today page ranks everyone’s week against the overtime line, with “Near overtime” within 4 hours of it and an Overtime badge past it. Approve a week to lock it, then download regular and overtime hours per person for whoever runs payroll. Settings take your overtime line, an optional daily line, the day the pay week starts and rounding to 5, 6 or 15 minutes. To see who will go over before Friday, ask GoodTaco:

Add a scheduled hours box to each person’s week. Show hours so far plus scheduled hours, and flag anyone who will go over my overtime limit before the week ends.

Already keep hours in an Excel workbook? Upload it with spreadsheet to app, no account needed to try. The records go into the app’s own built-in database, every version is saved with one-click rollback, and you can print any page from the browser.

GoodTaco is free to start. Paid plans start at $9 a month, and Pro at $49 a month includes 10 users; bigger teams talk to us about Enterprise.

See who’s heading into overtime while there’s still time to change the schedule. Start free, no credit card needed.

Frequently asked questions

What is an overtime tracker?

An overtime tracker is a sheet that adds up each person's hours for the workweek, separates regular hours from overtime, and shows who is close to the overtime line. A good one also works out the pay. Under the federal Fair Labor Standards Act, hours over 40 in a workweek are paid at no less than 1.5 times the regular rate for covered, nonexempt employees.

How do you track overtime in Excel or Google Sheets?

Make one row per person per week, with a column for each day. Total the days with SUM, then calculate overtime hours with =MAX(0,total-40). Add a column for hours still scheduled so you can see who will pass 40 before the week ends. The formulas in this guide use functions found in both Excel and Google Sheets.

How do you calculate overtime pay?

Multiply the regular rate by 1.5 for each hour over 40 in the workweek. Someone paid $20 an hour who works 45 hours earns 40 x $20 = $800 plus 5 x $30 = $150, so $950. If the person also earned a bonus that counts as pay for the hours worked, the bonus goes into the regular rate first.

When does overtime start?

Under federal law, after 40 hours in a workweek. A workweek is a fixed, repeating period of 168 hours that can start on any day. Some states also count daily hours. California pays overtime after 8 hours in a workday and double time after 12. Colorado counts hours over 12 in a day or 12 in a row, and Alaska over 8 in a day. Texas follows the federal weekly rule. Check your state labor department's page.

Does drive time between job sites count as work time?

Yes. The Department of Labor says travel from job site to job site during the workday is work time and must be counted as hours worked. The trip from home to the first site, and back at the end of the day, is ordinary commuting and is not work time. Count the hours between sites in the day's total before you check for overtime.

Do bonuses count toward overtime?

Nondiscretionary bonuses do, such as an attendance, safety or production bonus people know about in advance. They are added to the week's pay before dividing by the hours worked, which raises the regular rate. A truly discretionary bonus, decided at the sole discretion of the employer near the end of the period, is not included.

How long do you have to keep overtime records?

The Department of Labor says employers must keep payroll records for at least three years, and the records behind wage calculations, such as time cards and wage rate tables, for two years. The records must show hours worked each day and total hours each workweek. Check whether your state requires longer.

Free template

Start from the timesheet template

Get your own copy of a working timesheet app with sample records, ready to make yours. Free to start.

Use the template, free

Or turn your own sheet into an app

Keep exploring

Related guides and templates

View all articles

Template

Expense report template with approvals and reimbursements

Turn an expense report template into a real submission app, with manager approvals and reimbursement status your team can trust.

Template

Onboarding checklist template that tracks itself

Turn an onboarding checklist template into a live tracker, with task status, ownership, and progress your whole team can actually see clearly.

Template

Searchable employee directory template for Google Sheets

Build an employee directory template from the Google Sheet you already use: searchable contacts, departments, and roles that stay current. No rebuild required.

Industry

Build PTO Tracking Software From Your Spreadsheet

Build PTO tracking software, an employee onboarding system, and leave tracking from the spreadsheets your HR team already runs. No onboarding. No setup fee.