Timesheets to Excel for a Wage and Hour Claim

To get scanned timesheets to Excel for a wage and hour claim, convert each report type separately with OCR, export the tables, and reshape them so that one row holds one shift: employee, date, time in, time out and source page. Turn the clock times into real time values, then test every week's total before computing anything.

The examples follow an invented claim. Ines Delacroix worked as a dispatcher for Tallgrass Courier from 2024 to 2026 and says she was not paid for all her hours. Tallgrass has produced punch reports, handwritten correction sheets and payroll registers as scanned PDFs.

Punch codes are short, so read them in the header as well as the rows. After you export the reports for the wage and hour claim, search for overtime, for minimum wage notes, and for any code that marks an exempt employee. Confirm each time in and time out on the scan before you add the hours. A shifted column assigns the shift to the wrong day.

Which time and pay records does a wage and hour case collect?

A wage and hour case collects two families of record: time records, which show when work started and stopped, and pay records, which show what was paid for it. Timesheets, punch reports, schedules and edit logs are the first family. Payroll registers, pay stubs and rate change forms are the second. The claim lives in the gap between them.

Federal regulations give a checklist of what a covered employer is supposed to have. Among the items 29 CFR 516.2 lists at (a) are the "time of day and day of week on which the employee's workweek begins", the "regular hourly rate of pay for any workweek in which overtime compensation is due", "hours worked each workday and total hours worked each workweek", "total premium pay for overtime hours", "total wages paid each pay period" and the "date of payment and the pay period covered by payment". Read the section for the full list and for who it covers.

How long those records must be kept tells you how far back a production can reach. 29 CFR 516.5 begins "Each employer shall preserve for at least 3 years" and names payroll records first. 29 CFR 516.6, at (a)(1), puts a period of at least 2 years on "all basic time and earning cards or sheets on which are entered the daily starting and stopping time of individual employees". State law may require more records and longer periods, so check the state that governs your claim.

What each record contributes, and whether it goes into the workbook:

The rest of what an employer produces in an employment case, such as reviews, discipline and printed emails, is covered in the post on OCR for employment records.

Converting scanned timesheets and punch reports to spreadsheet rows

Convert a scanned punch report by uploading it on its own, exporting to Excel, and reshaping the export until each row is one shift. The report's own layout, with a block per employee or a row per week, is built for printing. A calculation needs a flat list: one line per person per day.

1. Separate the report types. Put punch reports in one file, payroll registers in another and handwritten sheets in a third. Work on copies.
2. Write down the control totals. Before converting, note the weekly or period totals printed on three or four pages. You will test the export against them.
3. Upload and export. Schema Law reads PDF, PNG or JPEG files up to 20 MB into text and tables. Its Excel export puts each table on its own sheet. Excel and CSV are included on every plan, and the pricing page shows how many pages each plan converts.
4. Stack the sheets. Copy every table into one sheet. Delete the repeated column headings, page numbers and report titles.
5. Carry the employee down. Where the report prints the name once at the top of a block, add a column and fill the name or employee number onto every row beneath it.
6. Unpivot weekly grids. If a timesheet has one row per week and a column per day, turn it into seven rows, one per day. A spreadsheet's unpivot or transpose tool does this, or copy and paste by hand for a short record.
7. Split in and out. A cell reading "6:58a - 3:34p" becomes two cells. Split shifts and lunch punches become extra rows or extra in and out columns. Pick one approach and keep to it.
8. Convert text to time. Converted times can arrive as text. Change them to real Excel times, check that "p" became afternoon, and format the columns as time.
9. Add the source page. Every row gets the production number of the page it came from.

How a converter finds columns on a scanned page, and why two of them sometimes merge, is explained in the guide to converting a scanned PDF to Excel.

Twelve-hour and twenty-four-hour clocks

Look at how the report prints time before you convert the text. "15:34" is unambiguous. "3:34" with no "a" or "p" is not, and a faint scan can lose the letter. Where the marker is missing, the shift's own logic settles it: a clock-out of 3:34 after a clock-in of 6:58 is the afternoon. Where logic does not settle it, mark the row and read the image.

Decimal hours are not minutes

Some reports print durations as hours and minutes ("8:36") and others as decimal hours ("8.60"). Those are the same shift. A column that mixes the two formats will add up to nonsense. Decide on decimal hours for totals, and convert with a formula that multiplies the time difference by 24.

Handwritten timesheets and a supervisor's corrections

Handwritten timesheets and corrected entries are the pages to type by hand. Recognition is weaker on handwriting than on print, and a crossed-out time with a new one written beside it holds two facts that both matter. Record the original entry, the corrected entry and who made the change, each in its own column.

Run the handwritten sheets through OCR anyway. The printed parts of the form, such as the week-ending date and the column labels, will come through, and the result gives you a grid to type into. Then read each time from the image. The post on handwritten ledgers to Excel covers handwritten figures in columns in more depth.

What a correction looks like on the page

• A time struck through with a different time written above or beside it.
• A time written over, so that one digit sits on top of another.
• Initials and a date in the margin, sometimes in a different ink.
• White correction fluid or tape, visible on the scan as a brighter patch.
• A note such as "forgot to punch" or "per manager".

None of those survives conversion as anything but stray characters, if it survives at all. Your workbook has to carry them deliberately.

Do not choose between the two times while you are building the sheet. Which one counts is a question of fact for the case. If the times were recorded in a timekeeping system and edited there, ask for the system's edit or audit report, and check whether it lists the before and after values. Treat that report as its own tab.

Photographed timesheets

A worker's own copies may be phone photos of a sheet on a clipboard. Shadows, angle and a curled page all cost legibility. Ask for the full-size photo, not a screenshot of it, and type the times from the picture while the client is available to tell you what an unclear digit says.

What columns does an overtime workbook need?

An overtime workbook needs a shift tab with one row per shift, a week tab that totals those shifts by workweek, a pay tab holding the payroll register, and an inputs tab for rates and the workweek's start day. Keeping the four apart lets a lawyer change an assumption without retyping a single clock time.

The federal starting point is the workweek. 29 CFR 778.105 defines an employee's workweek as "a fixed and regularly recurring period of 168 hours" and adds: "It need not coincide with the calendar week but may begin on any day and at any hour of the day." Under 29 U.S.C. 207, subsection (a)(1) sets overtime, for covered employees, on hours beyond forty in a workweek at "a rate not less than one and one-half times the regular rate". Exemptions, the meaning of "regular rate" and state rules that count overtime by the day are legal questions outside a spreadsheet. The workbook should make each of them a setting, not a buried assumption.

The shift tab

Two formula points save trouble. A shift that crosses midnight gives a negative result unless the formula adds a day when "out" is earlier than "in". And a date typed as text will not group into weeks, so test the date column with a sort before building on it.

The week tab

One row per employee per workweek: total hours from the shift tab, hours over forty, and any state daily overtime if your jurisdiction has it. A pivot table on employee ID and workweek start produces this and updates when a shift row is corrected.

The inputs tab

The hourly rate with its effective dates, the weekday and hour the workweek begins, any meal break length the employer says it deducted, and any rounding increment. Each input gets a source: a page number, a handbook section or an interrogatory answer.

Matching payroll registers to the hours on the timesheets

Match the register to the timesheets by putting both on the same calendar. Payroll is recorded by pay period and overtime is counted by workweek, so first split each pay period's paid hours into its workweeks, then set paid hours beside recorded hours for each week and look at the difference.

A payroll register prints one line or block per employee per pay date: regular hours, overtime hours, the rate for each, gross pay, deductions and net. Convert it the same way as a punch report and give it its own tab with the pay period start, end and pay date as real dates.

A worked week

Ms. Delacroix's punch report for the workweek beginning Monday 2 March 2026:

The register for the pay period that includes this week shows 40.00 regular hours and 2.50 overtime hours for it, or 42.50 paid. The gap is 2.83 hours.

Now test the explanations the records suggest. The handbook says a 30-minute meal period is deducted from every shift over six hours. Five shifts at half an hour is 2.50 hours, which leaves 0.33 hours, or 20 minutes. The punch report has no meal punches, so the workbook cannot say whether she took the breaks. It can say that the deduction accounts for 2.50 of the 2.83 hours and that the remaining 20 minutes disappears somewhere between the punches and the paycheck.

Rounding is one place to look for a remainder like that. The federal regulation on time clocks, 29 CFR 785.48, addresses at (b) the practice of recording time "to the nearest 5 minutes, or to the nearest one-tenth or quarter of an hour" and accepts it for enforcement purposes "provided that it is used in such a manner that it will not result, over a period of time, in failure to compensate the employees properly for all the time they have actually worked." A workbook with raw punches on one side and paid hours on the other is how you measure that over time: add a column that rounds each punch by the employer's stated rule, and compare the totals across every week in the claim. What the comparison means is for the lawyer.

Pay periods that split a workweek

A semi-monthly pay period, such as the 1st to the 15th, can end on a Wednesday. The register then spreads one workweek across two lines. Do not compare a pay period's hours with 80 or 86.67 and call the excess overtime. Total the shift tab by workweek, and use the register only for what was paid.

Certified payroll on a public construction job is a different document with its own form. The post on construction records covers it alongside superintendent daily logs and field reports.

How do you check converted punch times before computing unpaid hours?

Check converted punch times with tests that make a wrong digit stand out: every shift has a plausible length, every week's total equals the total printed on the report, and no day appears twice or goes missing. Then read a sample of rows against the page image, and every row that failed a test.

A misread in a time is quiet. "7:02" read as "1:02" is still a valid time, and Excel will happily subtract it. The post on why OCR gets numbers wrong explains which digits get confused and why. The tests that catch the result:

• Shift length. Flag any shift under one hour or over sixteen. Each is either real and interesting or a misread.
• Negative or zero hours. An "out" earlier than "in" on a day shift is an AM/PM error or a swapped pair.
• Printed totals. Where the report prints a daily or weekly total, compute your own and subtract. Any difference beyond rounding in the last decimal place points to the row with the error.
• Row counts. Count the shifts on five pages by eye and compare with the rows that came from those pages.
• Calendar gaps. List every date in the claim period beside the count of shifts on it. A scheduled day with no punch is a missing page, a day off or a missed punch, and you want to know which.
• Duplicates. The same employee, date and in-time twice means a page was scanned twice or a block was pasted twice.
• Minutes over 59. A time like "7:72" is a misread and will have failed to convert to a time value. Filter the time columns for cells still stored as text.
• Employee numbers. Confirm every ID on the shift tab exists on the pay tab, since one wrong digit moves a shift to another person.

How much to read by eye

Read every row a test flagged, every row typed from handwriting, and every row in any week you plan to use as an example in a demand or a filing. For the rest, read a random sample against the images and record the result. If the sample turns up errors the tests missed, the tests are not enough for this record and the reading has to go wider.

Schema Law shows the parsed pages on screen so the comparison can start before you export, and its home page says plainly that nothing marks a passage as uncertain. The list of export formats describes what the Excel and CSV files contain.

Keep a log tab in the workbook: which files were converted, on what date, which tests were run, how many rows were corrected and by whom. When the other side questions a number, the log and the source page column are the answer.