Extracting Structured Fields From a Time Sheet

The failure that matters on a time sheet is not a misread digit. It is a day with three punches instead of four, where summing the pairs you can make produces a plausible number that is four and a half hours short and raises nothing.

What makes a time sheet hard

A time sheet is a small document with almost no text on it, which makes it look like an easy extraction and hides where the difficulty actually is. The fields — employee, week ending, date, in, out, total — are trivially readable. The difficulty is that the sheet is a record of events and the thing you need is a duration, and the transformation between them has several ways to go wrong silently.

“Silently” is the operative word. If a name is misread, somebody notices. If Wednesday resolves to 4.03 hours instead of 8.53, the weekly total is 4.5 hours light, the number is entirely plausible, and nobody notices until the employee does — by which point it is a payroll correction and, depending on the jurisdiction, a wage compliance issue.

The design rule that follows is worth stating before anything else: an extracted day is either complete or it is not, and an incomplete day must carry a status rather than a number. Never impute a punch, never fall back to the scheduled shift, and never let a day with unmatched punches contribute zero to a total. Zero is a value; incomplete is not.

Time formats that look interchangeable

A single sheet can carry three notations, and two of them are numerically identical in some cells and not others:

• Clock time in 12-hour form without a meridiem. “7:00” in an IN column and “4:30” in an OUT column. The AM and PM are implied by the column and the shift, not written. An extraction that reads them literally computes a negative duration, and a pipeline that takes the absolute value of a negative duration has invented a number.
• Four-digit 24-hour time. “0730”, “1630”. Unambiguous, but easily read as an integer, and 1630 minus 730 is 900, which is not nine hours.
• Decimal hours. A TOTAL column of “7.5” means seven and a half hours. “7.45” in that column means seven hours and twenty-seven minutes, not seven forty-five. This is the confusion that produces small, consistent, hard-to-find errors, because 7.45 and 7:45 differ by eighteen minutes and both look correct.

Store every punch as a normalised time-of-day plus the date it belongs to, and store durations in minutes as integers. Do not store decimal hours as a float and do arithmetic on it: a 20-minute increment is 0.3333 recurring, and accumulating rounding across a fortnight produces a total that disagrees with the sheet by a minute or two, which then costs somebody an afternoon establishing that the discrepancy is meaningless.

Keep the raw string exactly as read alongside the parsed value. When a dispute arrives, the question is what the sheet said, and a normalised timestamp cannot answer it.

Pairing punches, and odd counts

On a sheet with explicit IN and OUT columns, the pairing is given by the layout. On a sheet with a single punch column — common on clock-card printouts and on handwritten sheets — you have an ordered list per day, and pairing is inference.

Wednesday punches:  08:02   12:04   12:33

pairing left-to-right:
  (08:02, 12:04) = 4h 02m
  12:33 unmatched

naive total for Wednesday = 4.03 h
plausible actual          = 8.53 h  (if the missing punch is a 17:05 out)

correct extraction output:
  { "date": "2026-02-11", "punches": 3, "status": "incomplete",
    "minutes": null, "reason": "odd_punch_count" }

Three punches means one is missing, and which one is missing is not determinable from the punches alone. It could be a missing OUT at the end of the day, in which case the pairs are (08:02, 12:04) and (12:33, —). It could be a missing IN after lunch, in which case the pairs are (08:02, 12:04) and (—, 12:33), which is not even a coherent reading. The extraction cannot resolve it and should not try.

What it can do is produce a useful review payload: the punches as read, the status, and the candidate reconstruction with its assumption named — “if the missing punch is an OUT and the scheduled shift ends at 17:00, the day is 8.4 hours”. A supervisor confirming a suggestion is a five-second task; a supervisor handed “incomplete” with no context has to find the employee.

Even punch counts are not proof of completeness. Six punches on a day with one break can mean three genuine segments, or it can mean a double punch where somebody badged twice within a few seconds. A pair whose duration is under a minute is almost always a duplicate rather than a minute of work, and collapsing those before pairing removes a large share of odd-count cases at the source.

Shifts that cross midnight

An OUT time earlier than the IN time on the same row means one of two things, and they need opposite handling. Either the shift crossed midnight — in at 22:00, out at 06:15, which is 8 hours 15 minutes — or a punch is missing and the row is broken.

The disambiguation is not arithmetic. It is context: does this employee work nights, does the sheet have a next-day column, does the following row start after the anomalous OUT time? A rule that always adds 24 hours to a negative duration will silently convert broken rows into long night shifts, which is the more expensive direction of error since it overstates hours rather than understating them.

Beyond the pairing question, a midnight-crossing shift also has to be attributed to a day, and the answer depends on the employer’s defined workday rather than on the clock. Hours are commonly attributed to the day the shift began, but not universally, and the attribution determines which workweek the hours land in — which in turn moves overtime, as the overtime page works through. Carry the attribution rule as configuration and record which rule was applied.

Daylight-saving transitions are the edge case beyond that. On the spring-forward day a shift from 22:00 to 06:15 is seven hours and fifteen minutes of elapsed time, not eight fifteen; on the autumn transition it is nine fifteen. If you compute durations by subtracting wall-clock times you will get the wrong answer twice a year in every jurisdiction that observes it. Convert to an absolute instant in the employee’s time zone before subtracting, and apply a date-field validation rule to the parsed values before either.

Footing against the printed total

Most time sheets carry totals the employee or a supervisor wrote: a daily total per row and a weekly total at the foot. These are a free checksum and they should be extracted as data to be verified, never as the answer.

Run three comparisons. Each day’s derived minutes against its printed daily total; the sum of derived days against the printed weekly total; and the sum of the printed dailies against the printed weekly total, which tests the sheet against itself. The third one is informative on its own — if the sheet does not foot internally, the problem is in the source document and no extraction quality will fix it.

Where derived and printed disagree, the printed figure often reflects rounding that the raw punches do not. Under the US Fair Labor Standards Act regulations, 29 CFR §785.48(b) permits recording time in rounded increments — to the nearest five minutes, tenth of an hour or quarter hour — provided the practice does not, over time, fail to compensate employees for all time actually worked. The text is published by the National Archives at eCFR §785.48. A quarter-hour rounding turns an 08:07 punch into 08:00 and an 08:08 punch into 08:15, so a printed total can legitimately differ from the raw derivation by several minutes a day.

The engineering consequence is to keep rounding out of extraction entirely. Extract raw punches, derive raw minutes, then apply the employer’s rounding policy as an explicit, separately recorded step. That way the record shows raw and rounded side by side, which is exactly what a wage audit asks for, and changing the policy does not require re-reading a single document.