Cleaning Timesheet and Payroll Data: Hours, Shifts and Overtime
Clean timesheet punches before payroll in DuckDB SQL and pandas: overnight shifts, daylight saving, missing clock-outs, duplicates, overlaps and weekly overtime.
By Tnas · Published · 8 min read
Timesheet data decides what people get paid, so a cleaning mistake isn't a rounding error in a dashboard: it's someone's wages. The raw punches look simple, a date, a clock-in time and a clock-out time, but they hide overnight shifts, daylight-saving changes, forgotten clock-outs, double submissions and overlapping entries. This post cleans a week of punches into paid hours and weekly overtime in DuckDB SQL, with the time-zone step repeated in pandas, and holds back every row that a person needs to check.
Key Takeaways
- Build full timestamps from date plus time, and move the clock-out to the next day when it is earlier than or equal to the clock-in.
- Convert local times to instants with the site's time zone before subtracting, or a shift across a daylight-saving change gets the wrong length.
- Never guess a missing clock-out or break. Hold the row with a reason and pay it once it is fixed.
- Flag duplicates, overlaps and implausibly long shifts instead of deleting or capping them.
- Total hours per employee per workweek, then split regular and overtime hours. Under the US federal rule, overtime starts after 40 hours in a workweek and can't be averaged across weeks.
What goes wrong in raw timesheet data
Here is a week of punches from three employees at one site in New York, invented for this example:
employee_id,team,work_date,clock_in,clock_out,break_minutes
A01,Warehouse,2026-03-02,07:00,16:30,30
A01,Warehouse,2026-03-03,07:00,16:30,30
A01,Warehouse,2026-03-03,07:00,16:30,30
A01,Warehouse,2026-03-04,07:00,16:30,30
A01,Warehouse,2026-03-05,07:00,16:30,30
A01,Warehouse,2026-03-06,07:00,16:30,30
B02,Warehouse,2026-03-02,22:00,06:00,30
B02,Warehouse,2026-03-03,22:00,06:00,30
B02,Warehouse,2026-03-04,22:00,,30
B02,Warehouse,2026-03-05,22:00,06:00,
B02,Warehouse,2026-03-07,22:00,06:00,30
C03,Front desk,2026-03-02,9:00,17:00,60
C03,Front desk,2026-03-03,06:00,02:00,30
C03,Front desk,2026-03-04,09:00,17:00,60
C03,Front desk,2026-03-04,16:00,20:00,0
Fifteen rows contain these problems:
| Problem | Rows | Wrong fix |
|---|---|---|
| Night shifts end the next day | B02 | clock_out - clock_in gives −16 hours |
| A shift crosses the daylight-saving change | B02 on 7 March | Counting 8 hours when 7 were worked |
| Missing clock-out | B02 on 4 March | Assuming a standard 8-hour shift |
| Missing break | B02 on 5 March | Treating blank as zero minutes |
| The same shift submitted twice | A01 on 3 March | Paying it twice |
| A 20-hour shift | C03 on 3 March | Capping it at 12 hours |
| Overlapping shifts | C03 on 4 March | Paying both in full |
| Mixed time formats | 9:00 and 09:00 |
Text comparison of times |
All code below was run with DuckDB 1.5.1 and pandas 3.0.2. The DuckDB output tables are trimmed of their type rows to fit the page.
Step 1: Build start and end timestamps
Load as text, number the rows so every exception can point back to the file, and combine date and time. SET TimeZone = 'UTC' fixes how instants are displayed, so results don't depend on the machine running the query. When the clock-out time is earlier than or equal to the clock-in time, the shift ended the next day:
SET TimeZone = 'UTC';
CREATE TABLE raw AS
SELECT row_number() OVER () AS row_id, *
FROM read_csv('timesheet.csv', all_varchar = true);
CREATE TABLE shifts AS
SELECT row_id, employee_id, team,
CAST(work_date AS DATE) AS work_date,
CAST(work_date AS DATE) + CAST(clock_in AS TIME) AS start_local,
CASE WHEN clock_out IS NULL THEN NULL
WHEN CAST(clock_out AS TIME) <= CAST(clock_in AS TIME)
THEN CAST(work_date AS DATE) + CAST(clock_out AS TIME) + INTERVAL 1 DAY
ELSE CAST(work_date AS DATE) + CAST(clock_out AS TIME)
END AS end_local,
TRY_CAST(break_minutes AS INTEGER) AS break_minutes
FROM raw;
SELECT row_id, employee_id, start_local, end_local
FROM shifts WHERE employee_id = 'B02' ORDER BY row_id;
┌────────┬─────────────┬─────────────────────┬─────────────────────┐
│ row_id │ employee_id │ start_local │ end_local │
├────────┼─────────────┼─────────────────────┼─────────────────────┤
│ 7 │ B02 │ 2026-03-02 22:00:00 │ 2026-03-03 06:00:00 │
│ 8 │ B02 │ 2026-03-03 22:00:00 │ 2026-03-04 06:00:00 │
│ 9 │ B02 │ 2026-03-04 22:00:00 │ NULL │
│ 10 │ B02 │ 2026-03-05 22:00:00 │ 2026-03-06 06:00:00 │
│ 11 │ B02 │ 2026-03-07 22:00:00 │ 2026-03-08 06:00:00 │
└────────┴─────────────┴─────────────────────┴─────────────────────┘
Casting to TIME reads 9:00 and 09:00 as the same time, which a text comparison wouldn't. The missing clock-out stays NULL; nothing here invents an end time.
The next-day rule is an assumption, and it can be wrong. C03's 06:00 to 02:00 becomes a 20-hour shift under it. That may be real, or 02:00 may be a mistyped 14:00. Step 3 catches it either way.
Step 2: Convert to instants before measuring
A local clock time isn't an instant. On 8 March 2026, clocks in New York jumped from 2:00 to 3:00, so a shift from 22:00 on the 7th to 06:00 on the 8th lasted seven hours, not eight. timezone('America/New_York', ts) interprets a plain timestamp as New York local time and returns an instant, a TIMESTAMPTZ (DuckDB, timestamp with time zone functions). Subtracting instants gives elapsed time:
CREATE TABLE timed AS
SELECT *,
timezone('America/New_York', start_local) AS start_at,
timezone('America/New_York', end_local) AS end_at,
date_diff('minute', timezone('America/New_York', start_local),
timezone('America/New_York', end_local)) / 60.0 AS elapsed_hours,
date_diff('minute', start_local, end_local) / 60.0 AS naive_hours
FROM shifts;
SELECT row_id, employee_id, start_local, end_local, naive_hours, elapsed_hours
FROM timed WHERE row_id = 11;
┌────────┬─────────────┬─────────────────────┬─────────────────────┬─────────────┬───────────────┐
│ row_id │ employee_id │ start_local │ end_local │ naive_hours │ elapsed_hours │
├────────┼─────────────┼─────────────────────┼─────────────────────┼─────────────┼───────────────┤
│ 11 │ B02 │ 2026-03-07 22:00:00 │ 2026-03-08 06:00:00 │ 8.0 │ 7.0 │
└────────┴─────────────┴─────────────────────┴─────────────────────┴─────────────┴───────────────┘
The naive subtraction pays an hour that wasn't worked; in November the error runs the other way and underpays an hour. If your timesheet system already exports timestamps with offsets, such as 2026-03-08T06:00:00-04:00, use them directly. The guide on dates and time zones for analysts and the lesson Instants, offsets and local time cover the general rules.
The same step in pandas uses tz_localize. Setting ambiguous and nonexistent to "raise" makes pandas stop on a local time that didn't exist or happened twice, such as a punch at 02:30 on the spring-forward night, instead of guessing:
import pandas as pd
ts = pd.read_csv("timesheet.csv", dtype=str)
start = pd.to_datetime(ts["work_date"] + " " + ts["clock_in"], format="%Y-%m-%d %H:%M")
end = pd.to_datetime(ts["work_date"] + " " + ts["clock_out"], format="%Y-%m-%d %H:%M")
end = end.where(end > start, end + pd.Timedelta(days=1)) # overnight shifts end the next day
tz = "America/New_York"
ts["start_at"] = start.dt.tz_localize(tz, ambiguous="raise", nonexistent="raise")
ts["end_at"] = end.dt.tz_localize(tz, ambiguous="raise", nonexistent="raise")
ts["elapsed_hours"] = (ts["end_at"] - ts["start_at"]).dt.total_seconds() / 3600
print(ts.loc[ts["employee_id"] == "B02", ["work_date", "clock_in", "clock_out", "elapsed_hours"]])
work_date clock_in clock_out elapsed_hours
6 2026-03-02 22:00 06:00 8.0
7 2026-03-03 22:00 06:00 8.0
8 2026-03-04 22:00 NaN NaN
9 2026-03-05 22:00 06:00 8.0
10 2026-03-07 22:00 06:00 7.0
Step 3: Hold rows that need a person, with a reason
Six kinds of problem shouldn't reach payroll until someone checks them (no row in this week trips the clock-change rule, so five appear below). One CASE gives each held row a single reason, in a fixed order, so the exception report is stable from run to run:
CREATE TABLE flagged AS
SELECT *,
CASE
WHEN end_local IS NULL THEN 'missing clock-out'
WHEN break_minutes IS NULL THEN 'missing break'
WHEN CAST(timezone('America/New_York', start_at) AS TIMESTAMP) <> start_local
OR CAST(timezone('America/New_York', end_at) AS TIMESTAMP) <> end_local
THEN 'local time skipped by clock change'
WHEN elapsed_hours > 16 THEN 'shift longer than 16 hours'
WHEN row_number() OVER (PARTITION BY employee_id, start_local, end_local ORDER BY row_id) > 1
THEN 'duplicate submission'
WHEN EXISTS (
SELECT 1 FROM timed o
WHERE o.employee_id = t.employee_id
AND o.row_id <> t.row_id
AND NOT (o.start_local = t.start_local AND o.end_local = t.end_local)
AND o.start_at < t.end_at AND t.start_at < o.end_at
) THEN 'overlaps another shift'
END AS exception
FROM timed t;
SELECT row_id, employee_id, work_date, start_local, end_local, elapsed_hours, exception
FROM flagged WHERE exception IS NOT NULL ORDER BY row_id;
┌────────┬─────────────┬────────────┬─────────────────────┬─────────────────────┬───────────────┬────────────────────────────┐
│ row_id │ employee_id │ work_date │ start_local │ end_local │ elapsed_hours │ exception │
├────────┼─────────────┼────────────┼─────────────────────┼─────────────────────┼───────────────┼────────────────────────────┤
│ 3 │ A01 │ 2026-03-03 │ 2026-03-03 07:00:00 │ 2026-03-03 16:30:00 │ 9.5 │ duplicate submission │
│ 9 │ B02 │ 2026-03-04 │ 2026-03-04 22:00:00 │ NULL │ NULL │ missing clock-out │
│ 10 │ B02 │ 2026-03-05 │ 2026-03-05 22:00:00 │ 2026-03-06 06:00:00 │ 8.0 │ missing break │
│ 13 │ C03 │ 2026-03-03 │ 2026-03-03 06:00:00 │ 2026-03-04 02:00:00 │ 20.0 │ shift longer than 16 hours │
│ 14 │ C03 │ 2026-03-04 │ 2026-03-04 09:00:00 │ 2026-03-04 17:00:00 │ 8.0 │ overlaps another shift │
│ 15 │ C03 │ 2026-03-04 │ 2026-03-04 16:00:00 │ 2026-03-04 20:00:00 │ 4.0 │ overlaps another shift │
└────────┴─────────────┴────────────┴─────────────────────┴─────────────────────┴───────────────┴────────────────────────────┘
Each rule is deliberate:
- Missing clock-out: the shift happened, but its length is unknown. Paying a default eight hours guesses at wages.
- Missing break: blank isn't zero. A blank might mean no break was taken, or that nobody recorded it.
- Local time skipped by a clock change: DuckDB's
timezone()resolves a local time that never existed, such as 02:30 on the spring-forward night, without an error. Converting the instant back to local time and comparing catches it. A punch in the repeated hour on the fall-back night converts cleanly but is ambiguous; only the device's offset can settle it, so ask for timestamps with offsets if your sites run night shifts. - Longer than 16 hours: the threshold is a policy choice for this example, not a legal limit. It catches mistyped times without deciding which digit is wrong.
- Duplicate submission: only the second copy is held, so the first is still paid. The window functions that do this are covered in SQL window functions for cleaning.
- Overlap: C03's 16:00 to 20:00 shift starts before C03's 09:00 to 17:00 shift ends. One of the punches is wrong, and the data alone can't say which, so both rows are held. The check compares each shift with every other shift for the same employee, not only the one before it, so a short shift inside a long one is caught too. Identical shifts are left to the duplicate rule.
The lesson Cross-field rules and the exercise Session start and end as instants practise checks like these.
Step 4: Total paid hours per workweek and split overtime
Under the US Fair Labor Standards Act, covered non-exempt employees must be paid overtime, at least one and a half times their regular rate, for hours worked over 40 in a workweek. The workweek is a fixed, recurring period of 168 hours that may start on any day, and averaging hours over two or more weeks isn't permitted (US Department of Labor, Fact Sheet #23). This example uses a workweek that starts on Sunday at midnight.
CREATE TABLE paid AS
SELECT employee_id, team,
work_date - CAST(dayofweek(work_date) AS INTEGER) AS workweek_start,
elapsed_hours - break_minutes / 60.0 AS paid_hours
FROM flagged
WHERE exception IS NULL;
SELECT employee_id, team, workweek_start,
sum(paid_hours) AS total_hours,
least(sum(paid_hours), 40) AS regular_hours,
greatest(sum(paid_hours) - 40, 0) AS overtime_hours
FROM paid
GROUP BY ALL
ORDER BY employee_id;
┌─────────────┬────────────┬────────────────┬─────────────┬───────────────┬────────────────┐
│ employee_id │ team │ workweek_start │ total_hours │ regular_hours │ overtime_hours │
├─────────────┼────────────┼────────────────┼─────────────┼───────────────┼────────────────┤
│ A01 │ Warehouse │ 2026-03-01 │ 45.0 │ 40.0 │ 5.0 │
│ B02 │ Warehouse │ 2026-03-01 │ 21.5 │ 21.5 │ 0.0 │
│ C03 │ Front desk │ 2026-03-01 │ 7.0 │ 7.0 │ 0.0 │
└─────────────┴────────────┴────────────────┴─────────────┴───────────────┴────────────────┘
dayofweek returns 0 for Sunday, so subtracting it gives each shift's workweek start date. A01 worked five 9-hour days after breaks, 45 hours, so five are overtime. Without the duplicate check, A01 would have shown 54 hours and 14 hours of overtime.
Two cautions before you reuse this:
- Shifts that cross the workweek boundary. This query assigns each shift to the week it starts in. B02's last shift starts on Saturday 7 March and runs into Sunday the 8th, the first day of the next workweek. Strictly, the two hours before midnight belong to the first week and the remaining five elapsed hours to the second. If an employee is near 40 hours, split shifts at the boundary instant before totalling.
- Federal rules aren't the only rules. Some states add daily overtime. In California, for example, hours over eight in a workday are paid at time and a half and hours over 12 at double time (California Labor Commissioner's Office, Overtime FAQ). Payroll owns the rule set; the cleaning job is to deliver correct hours per day and per workweek so any rule can be applied.
Hours here are decimals of an hour, which is exact for quarter hours. Keep pay amounts in exact decimal types, as the guide on money and exact decimals explains.
Step 5: Reconcile what was paid and what was held
Payroll needs to know that every punch was either paid or held:
SELECT (SELECT count(*) FROM raw) AS rows_in,
count(*) FILTER (WHERE exception IS NULL) AS rows_paid,
count(*) FILTER (WHERE exception IS NOT NULL) AS rows_held
FROM flagged;
┌─────────┬───────────┬───────────┐
│ rows_in │ rows_paid │ rows_held │
├─────────┼───────────┼───────────┤
│ 15 │ 9 │ 6 │
└─────────┴───────────┴───────────┘
Fifteen rows in, nine paid, six held with reasons. Send the held rows to supervisors with the row id, employee and reason, and rerun the same pipeline once they're corrected. The guide Validation rules and exception reports shows how to structure that report.
Checklist for the next pay period
| Check | Rule |
|---|---|
| Timestamps | Date + time; clock-out on or before clock-in moves to the next day |
| Time zone | Convert local times to instants with the site's zone before subtracting; hold local times skipped by a clock change |
| Missing values | Missing clock-out or break holds the row; never fill a default |
| Plausibility | Long shifts and overlaps are held, not capped or dropped |
| Duplicates | Identical shifts for one employee: pay the first, hold the rest |
| Overtime | Total per employee per workweek, then split regular and overtime |
| Boundaries | Split shifts that cross the workweek start if totals are near the threshold |
| Reconciliation | Rows in = rows paid + rows held |
Related reading
- Cleaning Sensor and IoT Readings: Gaps, Spikes and Unit Errors
- Why Excel Ruins Your Dates and IDs, and How to Prevent It
- Data Quality Checks Every Pipeline Should Run
Sources
- US Department of Labor, Wage and Hour Division: Fact Sheet #23, Overtime Pay Requirements of the FLSA, retrieved 2026-11-05
- California Department of Industrial Relations, Labor Commissioner's Office: Overtime FAQ, retrieved 2026-11-05
- DuckDB documentation: Timestamp with time zone functions
- DuckDB documentation: Window functions