Data processing

Uploaded timekeeping rows pass through a fixed sequence of stages before any legal threshold is evaluated. Each stage is deterministic, the same sequence applies to every analysis, and every exclusion is counted and reported — nothing is dropped silently:

• 1 · Validation, at upload — every row must carry an employee id and both punches, with clock-out after clock-in and a duration of 24 hours or less. Failing rows are excluded and counted, every failing reason recorded.
• 2 · File inclusion — an analysis reads only the files marked included; inclusion is user-controlled per file, so the row universe is explicit before anything else runs.
• 3 · The midnight split — a valid row that crosses midnight becomes two time segments, one per calendar day, so day-bounded rules evaluate on the workday the hours were worked. The only stage where the record count grows.
• 4 · Duplicate resolution — segments identical on employee, clock-in, and clock-out collapse to the first-ingested copy; repeats are flagged and set aside.
• 5 · Overlap resolution — where one employee’s segments overlap in time, the longest are kept and the rest are set aside, so the same minutes never count twice.
• 6 · Shift grouping — an employee’s remaining segments group into workday shifts; a gap at or above the split-shift threshold (default 4 hours, adjustable per analysis) starts a new shift.

Timestamps are read as local wall-clock times. An explicit UTC offset, if present, is not used to convert the time — conversion could shift a punch across midnight and move hours to the wrong workday. Employer rounding practices are not detected: the analysis evaluates timestamps exactly as recorded.

Every uploaded row is checked before analysis: it must carry an employee id, a clock-in, and a clock-out; the clock-out must come after the clock-in; and the duration must not exceed 24 hours. A row that fails any check is an Invalid Row — excluded from every downstream figure, with all of its failing reasons recorded, not just the first.

• Missing employee id, clock-in, or clock-out — the row cannot be attributed or measured.
• Clock-out not after clock-in — the interval is impossible as recorded.
• Duration over 24 hours — outside the single-shift bound; flagged rather than guessed at.

The record universe is described in three tiers, and every rate names its denominator: Rows are the uploaded universe; Valid Rows are the rows that passed validation; Time Segments are the analyzable intervals built from Valid Rows. An invalid-data rate is measured against Rows and a duplicate rate against Time Segments — a rate’s denominator is always the tier it filters from.

Only files marked included-in-analysis feed an analysis, and inclusion is user-controlled per file — so the universe behind every figure is an explicit choice, visible before any check runs.

Two time segments for the same employee that overlap in time would count the same minutes twice. Wherever a group of segments overlaps — including chains, where one segment overlaps a second and the second a third — the longest is kept first, then any others that do not overlap a kept segment; the rest are flagged and set aside. The kept segments feed shift construction, so the shift duration behind the meal thresholds never contains double-counted time.

Order matters: exact duplicates — identical employee, clock-in, and clock-out — are resolved first. A duplicate is a total overlap, and resolving it as a duplicate preserves the more specific reason; overlap resolution then handles the partial cases.

Back-to-back segments are not overlaps: when one segment ends at the exact minute the next begins, both are kept — touching endpoints are not overlapping time.

Timekeeping systems record punches to the minute, so observations arrive already minute-granular — the underlying seconds are unobserved (in statistical terms, censored). The analysis adds no rounding and no truncation of its own: every threshold comparison uses the exact recorded values (Donohue).

Concretely: a recorded 29-minute break is short — never rounded up to 30. A shift whose duration is 4 hours 59 minutes requires no meal period; at 5 hours 0 minutes it requires one. Three concepts stay distinct here: the minute granularity of the source data; this analysis’s no-rounding arithmetic; and an employer’s own rounding practices, which are a separate matter the analysis does not detect.

Follow one small upload — three employees on one workday, eight rows, in the employee_id · clock_in · clock_out shape timekeeping exports arrive in — through every data-processing stage. Illustrative data; nothing is dropped silently at any stage — every excluded record is counted and reported.

The upload, validated — 8 Rows, 7 Valid Rows

Every row must carry an employee id and both punches, with clock-out after clock-in and a duration of 24 hours or less. The result is recorded on the row itself — every failing reason, not just the first — and all times below are on the one workday, 2025-03-03:

Time Segments — duplicates and overlaps resolved, 7 → 5 Analyzable

Each Valid Row becomes one Time Segment dated to its calendar day (a row crossing midnight would split into two — none here does). Duplicates resolve first — identical employee, clock-in, and clock-out, as when the same records arrive in two exported files — then overlaps, where the longest segment is kept. Segments that sit apart are not overlaps: employee 102’s 11:30 clock-out and 12:00 clock-in are a gap, kept and resolved later as a meal candidate:

Shifts — 5 Analyzable Time Segments, 3 shifts

An employee’s segments separated by gaps below the split-shift threshold group into one workday shift, and the gaps between a shift’s segments become its meal-period candidates:

The records set aside along the way stay visible — invalid, duplicate, and overlapping counts are reported alongside the data they were excluded from, so the denominator behind every rate is explicit. These three shifts are exactly the ones the worked example from punches to premium carries through meal detection, premiums, and penalty estimates.