How to Track Overtime Hours (and the Four Things Spreadsheets Get Wrong)

Overtime is the easiest labour cost to predict and the one most agencies find out about last. The hours are known days in advance — they are sitting in the schedule — but the total usually arrives with payroll, by which point every decision that created it has already been made.

A spreadsheet is the normal first answer, and it is a reasonable one. But overtime has four rules that spreadsheets get wrong by default, and each of them is a real wage-and-hour exposure rather than a rounding error.

Start with the only thing that matters: the workweek

Overtime under the federal FLSA is calculated per workweek — defined by regulation as "a fixed and regularly recurring period of 168 hours — seven consecutive 24-hour periods", which "may begin on any day and at any hour" (29 CFR 778.105). Over 40 hours in that workweek is overtime at one-and-a-half times the regular rate (US Department of Labor, Overtime Pay).

Two things follow, and both are routinely missed:

Your workweek is not your pay period. Most agencies run a biweekly pay period. Overtime is never calculated across two weeks — 50 hours one week and 30 the next is 10 hours of overtime, not "80 hours, no overtime." The regulation is titled "Each workweek stands alone" and says the Act "does not permit averaging of hours over 2 or more weeks", giving almost this exact example — and it applies "regardless of whether [the employee] is paid on a daily, weekly, biweekly, monthly or other basis" (29 CFR 778.104).

Your workweek has a defined start day, and it must be fixed. It can begin on any day — Sunday and Monday are both common — but it has to be consistent, and it determines which side of the line a Saturday-night shift falls on. An overnight shift that starts Saturday 23:00 and ends Sunday 07:00 sits in different workweeks depending on where you drew the boundary, and that changes who is owed overtime.

Decide the boundary once, write it down, and build every sheet around it.

What the sheet needs

At minimum, one row per person per day, not per week:

Column	Why
Date	Overtime is a weekly total built from daily parts
Staff name	—
Hours worked	The raw number
Shift type	Sleep and live-in hours may not count the same way
Week starting	Calculated from your fixed boundary, never typed
Weekly total to date	A running sum within that workweek
Over threshold?	The flag that should make someone act

The column people leave out is week starting, and it is the one doing all the work. Without it you cannot group days into the right weeks, and every weekly total is guesswork.

Use a formula so the boundary is applied consistently:

• Week starting (Sunday weeks) — =A2-WEEKDAY(A2,1)+1, where A2 is the date.
• Week starting (Monday weeks) — =A2-WEEKDAY(A2,2)+1.
• Weekly total to date — =SUMIFS(C:C,B:B,B2,E:E,E2), summing hours for that person in that workweek.
• Over threshold — =IF(F2>40,F2-40,0).

Sort by weekly total descending and the top of the sheet is the conversation you need to have this week, not next payroll.

The four things spreadsheets get wrong

1. Averaging across the pay period

Covered above, and worth repeating because it is the expensive one. A biweekly sheet that sums 80 hours and reports no overtime is wrong whenever the two weeks are uneven. Group by workweek, always.

2. Assuming overtime only starts at 40

The 40-hour weekly rule is the federal floor, not the whole picture. Some states require daily overtime as well:

• California requires overtime over 8 hours in a day, double time over 12 hours in a day, and overtime on the 7th consecutive day worked in a workweek — the first 8 hours at 1.5×, beyond 8 at 2× (California DIR, Overtime; Labor Code § 510).
• Alaska requires overtime over 8 hours in a day as well as over 40 in a week (Alaska Statutes § 23.10.060, which has its own exemptions).
• Nevada requires overtime over 8 hours in a workday for employees paid less than 1.5× the state minimum wage, unless the employee has agreed to a scheduled four-day, 10-hour week (NRS 608.018).
• Other states have their own rules. Check your state's labour department rather than assuming the federal 40-hour week is the whole story.

In a 24-hour residential setting, daily overtime matters far more than in most industries, because 12-hour shifts are normal. A staff member working three 12-hour shifts is at 36 hours — no federal overtime at all — while in California each of those days has already crossed the daily threshold.

A sheet built on "sum the week, subtract 40" cannot see any of this.

3. The 7th consecutive day

Where it applies, this one is invisible to a weekly total. Someone can work seven days and never reach 40 hours — seven 5-hour shifts is 35 — and still be owed premium pay for the seventh day. The trigger is consecutive days worked within the workweek, not the hours total, so no amount of summing column C will reveal it.

Note the subtlety: it means seven days within your defined workweek, not any rolling seven-day window. An employee who works Saturday and then the following Monday through Saturday has worked seven days in a row on the calendar, but not a full workweek without a day off. The workweek is the unit of measurement.

4. Reporting the past instead of preventing it

This is the structural one. A tracking spreadsheet is a record of overtime that has already happened. Every hour in it was authorised days earlier, by someone filling a shift who could not see the weekly total at the moment they made the decision.

You can build a perfect sheet and still spend exactly as much on overtime, because the sheet and the schedule are different files. Tracking is not controlling.

Sleep and live-in hours complicate the total

One residential-specific wrinkle: not all hours present are necessarily hours worked. Under specific FLSA conditions, some sleep time during a long shift may be excludable from paid hours — which changes the weekly total that overtime is calculated from.

Get this wrong in either direction and the overtime figure is wrong too: exclude sleep time that does not qualify and you underpay; count time you did not need to and you inflate the cost. It is covered properly in FLSA sleep time rules for group homes.

A practical routine

If you are staying on a spreadsheet, this is the minimum that makes it useful rather than decorative:

1. Fix the workweek start day and put it at the top of the sheet in writing.
2. Enter hours daily, not at the end of the period. A weekly total that appears on Friday is a report; one that appears on Tuesday is a decision.
3. Check it mid-week, around day 4. That is the last point at which the remaining shifts can still be moved.
4. Look at who is at 30–35 hours, not who is over 40. By the time someone is over, the money is spent.
5. Confirm your state's rules rather than assuming the federal 40-hour floor is the whole story.

How DSPsystem handles this

The difference is when the number appears.

Hours worked to date are shown while the assignment is being made — so choosing to pay overtime is a deliberate decision rather than something discovered at payroll. That single change is where the saving comes from; it is the same information a spreadsheet holds, moved to the moment it can still affect the outcome.

The thresholds are configurable rather than hardcoded at 40: a weekly threshold, an optional daily threshold, an optional double-time threshold, and the 7th-consecutive-day rule, each switchable per organisation — because an agency in California needs all four and an agency in Ohio needs one. The workweek start day is configurable too, so the boundary matches the one your payroll actually uses instead of an assumed Sunday.

And because sleep, live-in and awake-overnight are distinct shift types rather than one generic night block, the hours going into the overtime calculation are recorded as what they actually were.

None of that removes the need to understand the rules. It removes the need to rebuild them in formulas every time someone adds a home.

---

Sources

Every rule above was checked against the primary text on 2026-10-18:

• 29 CFR § 778.104 — Each workweek stands alone (no averaging across two or more weeks)
• 29 CFR § 778.105 — Determining the workweek (a fixed, recurring 168-hour period)
• US Department of Labor — Overtime Pay — the federal 40-hour, time-and-a-half rule
• California Labor Commissioner — Overtime FAQ — daily overtime, double time and the seventh consecutive day (Labor Code § 510)
• Alaska Statutes § 23.10.060 — Payment for overtime (8 hours a day, 40 a week)
• Nevada Revised Statutes § 608.018 — Compensation for overtime

This article is general information, not legal advice. Overtime rules turn on specific facts and exemptions, and they change. Confirm your own setup with your state labour department or an employment lawyer.