Payroll
How to reconcile client hour sheets with ADP without losing an evening
Reconciling client hour sheets with ADP requires matching on a stable identifier (Employee ID or SIN last 4), never on names alone, because married names and variant surnames break name matching. After matching, sum hours per employee ID, drop report-total rows, and verify the worker count against ADP before import.
Every client formats hours differently: combined name cells, subtotal rows, separate earning titles for regular and overtime. The pattern is the same: parse the source, match on ID, sum per worker, and verify the count. One mismatch discovered during import is much cheaper than one caught during payroll reconciliation.
Why is reconciliation hard?
Every client exports their hours differently. One sends a CSV with clean rows. Another sends a combined spreadsheet with subtotals. A third includes both regular and overtime hours under different earning titles on the same row, and you have to split them. Worse, they use their own names for the workers, not yours. A worker you know as 'Sarah Singh' might be listed as 'S. Singh' by one client and 'Sarah Kaur Singh' by another. If you match on names, you'll either split one worker across two records (if you're cautious) or accidentally merge two different people (if you're not). The reconciliation step exists to catch these before they become payroll errors.
What is the rule for matching?
Match on a stable identifier that never changes: Employee ID or SIN last 4. Ignore the name. Names are soft confirmations only. The rule is: if the ID is unique and present in both your roster and the client's export, match them. If you have the ID but the name is different, the ID wins. If the ID is missing, mark it as ambiguous and ask the client or the worker to clarify.
Why SIN last 4 and not full SIN? Because the full SIN is sensitive data and should not live in client-visible reports. The last 4 digits are unique enough within a small agency: the probability of two workers sharing the same last-4 is low. Treat any collision (two workers with the same last-4) as ambiguous and ask for clarification.
What does a reconciliation workflow look like?
- Export or receive the client's hour sheet. Look at the format: is it a CSV, an Excel file, a combined multi-sheet workbook?
- Scan the sheet for metadata rows. Identify the column headers. Note any subtotal rows or grand total rows that are not worker records.
- Extract the worker records. A worker record has a name, an ID (if present), and hours. It is not a subtotal, a report header, or a blank row.
- For each worker record, extract: the ID (Employee ID from the client, or the Client's name for that worker), the hours (regular and/or overtime), and any earning title or shift code if the client uses one.
- Match to your roster: look up the worker by ID first. If the ID is missing or ambiguous, note it as a flag.
- Sum hours per worker. If the client has split the same worker across multiple rows (one for regular, one for overtime), combine them.
- Verify the total: count how many unique workers are in the client's export. Compare that to how many you expected. If the count does not match, stop and ask the client why.
- Export the reconciled hours in a format your payroll system accepts (usually CSV with columns: ID, name, regular hours, overtime hours, etc.).
- Import into ADP or your payroll system. Let the system warn you of any mismatches.
What are the common traps?
| Trap | Symptom | Prevention |
|---|---|---|
| Name-match collision | Two workers share a last name (e.g., Singh). You match both to the first 'Singh' in the client export and lose one worker's hours. | Never match on name alone. Always use Employee ID or a unique hash. If the ID is missing, mark as ambiguous and ask. |
| Subtotal double-count | A client includes a 'Total Hours' or 'Week Subtotal' row. You count it as a worker and inflate the hours. | Scan the export for rows with blank IDs or text like 'Total', 'Subtotal', 'Report Total'. Drop them before summing. |
| Split OT rows | One client puts regular hours on one row and OT hours on another row for the same worker, each under a different earning title. | Group by ID, not by row. Sum all hours for the same ID, then split into regular and OT based on the earning title. |
| Variant surnames | A worker was listed as 'Jane Smith' last month but 'Jane Smith-Jones' this month after a marriage. You create a duplicate record. | Use ID, not name. The ID persists across name changes. |
| Stale Employee IDs | A client's list of your workers is a year old and includes a worker who left in March. Hours are reported under their old ID. | After you verify the import, ask: do all the IDs in this export match current workers in ADP? If one does not, flag it. |
How does ADP fit into this?
ADP (or Wagepoint, Payworks, or QuickBooks) is your payroll system of record. It holds your active workers, their tax information, their pay rates, and their hours. Before you import reconciled hours, your workers must already exist in ADP with a unique File Number or ID. The reconciliation process is the step *before* ADP: you take the messy client exports, clean them up, and match them to the roster. Only then do you import into ADP.
ADP will reject or warn on any import that has a mismatch: an ID not in the system, a duplicate ID, or a format error. Those warnings are helpful. Do not ignore them. Stop, go back to the reconciliation step, and fix the source data before re-importing.
What is the checklist before you import?
- All worker records have a unique, valid Employee ID or File Number (matching the ID in ADP).
- The total number of unique workers in the export matches the number you expected.
- No subtotal or report-total rows are included.
- Regular and overtime hours are separated (either in separate columns or clearly labeled by earning title).
- All hours are positive numbers with no text or symbols (except a decimal point).
- No worker appears on more than one row (i.e., split rows are already combined).
- You have called the client to verify any ambiguous or missing IDs.
- The sum of hours in your reconciled file matches the sum of hours in the client's original export.
Common questions
Can I match on the worker's name instead of ID?
Name matching is risky. Surnames change with marriage, and two workers can share a surname. If you must use a name, combine it with another field (DOB, hire date, or phone) to make it unique. Better: use Employee ID or SIN last 4, which are stable and unique.
What if a client does not include an Employee ID in their export?
Ask them for it. If they do not have one, assign a temporary reference code you share with them (e.g., 'CLI-001' for their first worker). Document the mapping so you can reconcile against them weekly.
Can subtotal rows stay in the export if I do not count them?
No. Remove them before import. Subtotal rows can confuse the import process or be accidentally counted by a script. Clean the data first, then import.
How do I handle a worker who worked for two different clients in one week?
Each client exports their own hours. Reconcile each export separately. When you import both into ADP, sum the hours for that worker across all clients. ADP can handle multiple imports per worker per pay period.
What if the client's total hours do not match what the worker says they worked?
Ask the client to verify. The client's timesheet or system is usually the source of truth. If the worker disputes it, that is a separate conversation. Do not adjust the hours unilaterally.
Should I reconcile every week, or only when I import?
Reconcile before every import. The process is your quality gate. It is cheaper to catch a mismatch now than to correct a payroll error and redo the payment.
Sources
Keep reading
Kordis handles this work for Canadian staffing agencies.
Shifts filled, credentials tracked, worker texts answered, payroll evenings shortened. Set up on your own data.
Start your two weeks freeOn your own data. No credit card.