Template

Tracking Overtime Spreadsheet:Daily Hours Columns, MOD and MIN Formulas, FLSA Weekly and California Daily Overtime, and a Filled Seven-Day Week

Tracking overtime spreadsheet: columns and formulas for FLSA weekly and California daily overtime, double time and the seventh day, with a filled Fresno week.

Updated 10 min read

Download .xlsxExcel spreadsheet · 13 KB · Free, no sign-up

A tracking overtime spreadsheet turns clock-in and clock-out times into hours worked per day, then splits each day and the week into regular, time-and-a-half and double-time hours. Federal law counts overtime by the workweek alone. California adds daily overtime, double time and a seventh-day rule. The columns and formulas below are filled for one technician's week in Fresno, California.

The layout is one of the free field service templates kept by FieldRanger, field service software that runs inside HubSpot. It works in Excel and Google Sheets, one tab per employee per workweek. A federal-only week, and what a GPS clock-in records, are covered in the guide to the time clock with GPS tracking. This page sets out the spreadsheet, built for a state where the day matters as much as the week, and offers it as a free .xlsx download with no sign-up.

Overtime rules the spreadsheet applies

The formulas encode two sets of rules, and the spreadsheet keeps them in separate columns so each can be checked.

Overtime rules encoded in the spreadsheet, federal and California
RuleFederal (FLSA)California
Weekly overtime1.5 times the regular rate for hours over 40 in the workweekSame, counting only hours not already paid as daily overtime
Daily overtimeNone1.5 times for hours over 8, up to and including 12, in a workday
Double timeNone2 times for hours over 12 in a workday
Seventh consecutive dayNone1.5 times for the first 8 hours, 2 times after 8
PeriodA fixed, recurring 168-hour workweekWorkweek, plus a fixed 24-hour workday
AveragingNot permitted across weeksNot permitted across workdays

The federal column comes from the Department of Labor. Overtime is due for "hours worked in excess of 40 in a workweek at a rate not less than time and one-half their regular rates of pay". The workweek is "a fixed and regularly recurring period of 168 hours", which "may begin on any day and at any hour", and "averaging of hours over two or more weeks is not permitted" (DOL, Fact Sheet #23: Overtime Pay Requirements of the FLSA, revised October 2019). The same fact sheet states the Act requires no overtime "for work on Saturdays, Sundays, holidays, or regular days of rest, as such". Under 29 CFR 778.104, 30 hours one week and 50 the next still owe overtime for the second week.

The California column comes from the Labor Commissioner. Its overtime page sets time and a half "for all hours worked in excess of eight hours up to and including 12 hours in any workday" and "for the first eight hours worked on the seventh consecutive day of work in a workweek". Double time applies "for all hours worked in excess of 12 hours in any workday" and for hours over eight on the seventh consecutive day (DIR, DLSE, Overtime FAQ, checked November 2026). The DLSE glossary defines a workday as "a consecutive 24-hour period beginning at the same time each calendar day" (DLSE Glossary, checked November 2026).

Tracking overtime spreadsheet columns and formulas

Rows 6 to 12 hold the seven days of one workweek, Sunday first. The formulas are shown for row 6 and copy down.

Tracking overtime spreadsheet day columns, contents and formulas (row 6 shown)
ColumnHeaderContentsFormula
ADateThe workday's dateTyped
BDayDay name=TEXT(A6,"ddd")
CJob or siteJob number or site from the clock recordTyped
DClock inTime valueTyped or pasted
EClock outTime valueTyped or pasted
FUnpaid breakMinutes clocked out for mealsTyped
GHours workedDecimal hours, overnight shifts included=ROUND(MOD(E6-D6,1)*24-F6/60,2)
HDay of workweek1 to 7=ROW()-5
ISeventh consecutive dayTRUE only on day 7 when all seven days were worked=AND(H6=7,COUNTIF($G$6:$G$12,">0")=7)
JDaily regularHours not paid as daily or seventh-day premium=IF(I6,0,MIN(8,G6))
KDaily 1.5xHours 8 to 12, or the first 8 on the seventh day=IF(I6,MIN(8,G6),MAX(0,MIN(G6,12)-8))
LDaily 2xHours over 12, or over 8 on the seventh day=IF(I6,MAX(0,G6-8),MAX(0,G6-12))
MSourceClock record or timesheet referenceTyped

MOD returns "the remainder after number is divided by divisor" (Microsoft Support, MOD function). A clock-out at 1:00 AM after a 6:00 PM start gives a negative difference, and MOD of 1 turns it into the fraction of a day that elapsed. Multiplying by 24 converts that fraction to hours.

Tracking overtime spreadsheet week summary (values in column G)
RowLineFormula
14Total hours worked=SUM(G6:G12)
15Daily regular hours=SUM(J6:J12)
16Weekly overtime, California=MAX(0,G15-40)
17Regular hours paid=G15-G16
18Time-and-a-half hours=SUM(K6:K12)+G16
19Double-time hours=SUM(L6:L12)
20Regular rateTyped
21Gross pay, California=ROUND(G17*G20+G18*G20*1.5+G19*G20*2,2)
22Hours over 40, federal check=MAX(0,G14-40)
23Gross pay, federal rules only=ROUND(MIN(G14,40)*G20+G22*G20*1.5,2)
24Check=G17+G18+G19-G14, which must equal 0

Row 16 is the California-specific line. Weekly overtime counts row 15, not row 14, because an hour already paid as daily overtime cannot count toward the 40. The DLSE states the rule: "Once an hour is counted as an overtime hour under some form of overtime," "it cannot be counted as an hour worked for the purpose of another form of overtime" (DLSE, AB 60 update, December 23, 1999). Its own sample, 12 hours on four days, gives 16 daily overtime hours, 32 regular hours and no weekly overtime.

Filled overtime week: HVAC service technician, Fresno, California

The technician took an on-call repair on Sunday, a long install on Wednesday and a Saturday half day, and so worked all seven days of the workweek. The workweek runs from Sunday 12:00 AM. The employer, technician, jobs, times and the $31.50 rate are illustrative.

Filled day rows, workweek of October 4 to 10, 2026 (sample technician and rate)
DateDayJobInOutBreakHours7th dayRegular1.5x2x
Oct 4SunJ-5102 on-call1:00 PM3:30 PM02.50FALSE2.500.000.00
Oct 5MonJ-5108, J-51107:00 AM5:30 PM3010.00FALSE8.002.000.00
Oct 6TueJ-51147:00 AM3:30 PM308.00FALSE8.000.000.00
Oct 7WedJ-5117 install7:00 AM8:15 PM3012.75FALSE8.004.000.75
Oct 8ThuJ-51217:00 AM3:30 PM308.00FALSE8.000.000.00
Oct 9FriJ-5125, J-51267:00 AM4:30 PM309.00FALSE8.001.000.00
Oct 10SatJ-51308:00 AM2:00 PM06.00TRUE0.006.000.00
Filled week summary (sample rate)
LineCalifornia rulesFederal rules only
Total hours worked56.2556.25
Regular hours paid40.0040.00
Time-and-a-half hours15.50: 13.00 daily and seventh-day, 2.50 weekly16.25
Double-time hours0.750.00
Gross pay at $31.50$2,039.63$2,027.81

The California total breaks down as 40.00 x $31.50 = $1,260.00, 15.50 x $47.25 = $732.38, and 0.75 x $63.00 = $47.25. The federal total is 40.00 x $31.50 plus 16.25 x $47.25. The two sheets agree on 16.25 premium hours. They differ only in Wednesday's 0.75 hour, paid at double rather than time and a half: 0.75 x $15.75 = $11.81, shown as $11.82 after each total rounds.

A shorter week moves further apart. Four 10-hour days total 40 hours: no federal overtime, and 8 California daily overtime hours.

Hours worked in Excel: shift rows, time values and weekly totals

A reliable hours sheet starts from times, not from typed hour counts. Each shift becomes one row with the employee, the date, the job and the clock-in and clock-out as time values. A column computes decimal hours with the MOD formula, so a shift past midnight still adds correctly. Unpaid breaks are entered as minutes clocked out, not netted by hand.

Totals come from formulas over the rows. A "week start" column holds =A6-WEEKDAY(A6)+1, the Sunday of each row's week. SUMIFS then totals hours by employee and week start, and the same pattern totals by workday when one day holds several jobs. The clock record's ID stays in its own column, so every hour traces to a punch.

Federal rules set what the sheet must hold. An employer records the "time of day and day of week on which the employee's workweek begins", the "hours worked each workday and total hours worked each workweek", and the "total premium pay for overtime hours" (29 CFR 516.2). Time cards showing daily starting and stopping times are kept "for a period of at least 2 years" under 29 CFR 516.6.

The sheet has limits. It assumes one hourly rate. A nondiscretionary bonus, a second rate for different work, or a shift differential changes the regular rate, which "includes all remuneration for employment" apart from excluded payments, per Fact Sheet #23. California meal and rest period premiums are outside it too.

Weekly overtime instructions from clock record to check row

  1. Set the workweek start day and hour once, and keep it fixed.
  2. Paste clock-in and clock-out times from the clock record into columns D and E, one row per day or per job.
  3. Enter unpaid breaks in minutes, and the clock record ID in column M.
  4. Let columns G to L compute; do not overwrite a formula with a typed figure.
  5. Read row 16: weekly overtime counts only hours in column J.
  6. Enter the regular rate in row 20 and read gross pay in row 21, with the federal check in row 23.
  7. Check row 24 equals 0, column G matches the clock record's daily totals, and column I is TRUE only on a seventh worked day.

Equipment tracking spreadsheet and warehouse inventory tracking spreadsheet

The overtime sheet shares its structure with two other tracking sheets. The equipment tracking spreadsheet keeps one row per tool movement, with the person and date, as this sheet keeps one row per shift. The warehouse inventory tracking spreadsheet totals stock by location with SUMIFS, the same function that totals hours by week here.

GPS time tracking app records as spreadsheet input

The spreadsheet is only as accurate as columns D and E. In FieldRanger, the assigned technician clocks in and out with time logged against each job, and each clock-in and clock-out records where the technician was, so hours match job sites. Any report downloads as CSV or Excel. The exported punches paste into columns D and E, with the job number in column C. Time is logged per job, so a day with three jobs exports as three punch pairs, and SUMIFS by date rolls them into the day row. Each day is its own job, so a two-day install appears as two dated jobs. The GPS time tracking app page describes the clock-in records.

FieldRanger does not run payroll, so this sheet, or a payroll service, applies the overtime and California's daily and seventh-day rules to the exported hours.

Frequently asked questions

How to track overtime in an Excel spreadsheet?

Record clock-in and clock-out times for each day, compute hours with =ROUND(MOD(out-in,1)*24-break/60,2), and total the workweek. Federal overtime is hours over 40 in the workweek; in California, add daily columns for hours over 8 and over 12 and a seventh-day flag, then count weekly overtime only from hours not already paid as daily overtime.

What is the best way to track hours worked in Excel?

One row per shift with the employee, date, job, clock-in and clock-out stored as time values, a formula column for decimal hours using MOD for overnight shifts, a week-start column, and SUMIFS totals by employee and week. The clock record's ID stays on each row.

How is overtime calculated in California?

Time and a half for hours over 8 up to 12 in a workday, over 40 in a workweek, and the first 8 hours on a seventh consecutive day of work; double time for hours over 12 in a workday and over 8 on the seventh day.

Do daily overtime hours count toward weekly overtime in California?

No. The Labor Commissioner does not allow pyramiding: an hour counted as daily overtime is not counted again toward the 40 weekly hours. In the sample week, 42.50 non-premium hours produce 2.50 weekly overtime hours.

Does federal law require overtime after 8 hours in a day?

No. The FLSA requires overtime for hours over 40 in a workweek and none for long days, weekends or holidays as such; daily overtime comes from state law such as California's.

Is there a free overtime tracking spreadsheet template?

Yes. The page offers a free .xlsx download with no sign-up: its Template sheet carries the day rows 6 to 12 and the week summary in column G, and its Sample sheet holds the filled sample week. It opens in Excel or Google Sheets.

Wednesday's 0.75 hour

The sample week turns on one cell: L9, the 0.75 hour after the twelfth on Wednesday's install. Federal rules pay it at time and a half; California pays it double, and that single difference separates $2,039.63 from $2,027.81. A dispatcher watching that install run toward its twelfth hour is watching the one hour of the week California prices at double. Work order and invoice forms for the jobs behind those hours are among the free field service templates; plans are on pricing, and Start free trial runs 14 days without a card.

FieldRanger runs field service inside HubSpot

Estimates, scheduling and dispatch, a technician app and invoicing on the HubSpot records sales already keeps. Every plan starts with a free trial.

Book a demo