Timesheet reconciliation: check hours before payroll

Approved timesheets can still end up wrong in the saved records. A shift is missing. Another is saved twice. A break sits on the wrong day. Each row was fine when somebody approved it, so nobody looks again until the difference surfaces somewhere else.

Timesheet reconciliation, as this article uses the term, is a row-by-row comparison made before payroll preparation. It sets the approved source for each shift against the value saved for it, and produces a list of what is missing, duplicated or different, with a named person to decide each item. It stops at checked hours. A full payroll reconciliation, pay rules, tax and payment runs are out of scope, and so is anything statutory.

It is written for recruitment and staffing operations, though the method suits any team that keys hours from one record into another. The checklist fields, exception table and ownership rules are our recommendations, not a published standard. The technical sources we cite are method documentation, mostly from Microsoft Learn. None is a UK staffing standard or a legal duty.

Four checks that all get called reconciliation

Separate these first, because each can pass while another fails.

The Government Data Quality Framework, published on 3 December 2020, defines completeness as the degree to which records are present, uniqueness as the degree to which there is no duplication in records, and accuracy as the degree to which data matches reality. GOV.UK: the Government Data Quality Framework is method guidance, not a duty on agencies. It adds: "It is important not to confuse the completeness of data with its accuracy. A complete data set may have incorrect values in fields, making it less accurate." Our reading is that a batch can be complete and wrong, so each check needs its own evidence.

The phrase means other things elsewhere. In our review on 2 October 2026, ActivTrak's timesheet reconciliation page (undated) used it for employee-reported hours against activity data, HayatCMS's care agency page (30 August 2026) for a planned rota against delivered hours, and QuickBooks UK's payroll guide (3 February 2025) for a payroll register against planned pay. None of those is the comparison here. We name them only to show the range of meanings, and we have not tested their products. Checking pay itself is outside this article.

Choose a record key before you compare anything

Matching on a name fails when two workers share one, or one worker has two shifts in a day. Matching on totals fails differently: if one shift is missing and another is saved twice, and they are the same length, the weekly total still agrees. That is our reasoning, not a published finding.

A usable key identifies one shift for one worker. We suggest a worker identifier, date, site and shift start time, plus a source reference. Treat it as a local decision. Worker, date and site alone will flag a legitimate split shift as a duplicate, and an overnight shift crosses a date boundary, so test your key on both.

Microsoft's merge queries overview (page review date April 2026) describes joining two tables on matching values from one or several columns, and says the columns must be the same data type or the merge might not give correct results. Microsoft's data types page (updated 14 August 2026) says Power Query uses locale when reading text values, and shows how UK-formatted dates can be misread under a US locale. Its fuzzy matching page (August 2025) says fuzzy matching depends on a similarity threshold, and the merge overview says it works only over text columns. Our inference: use a near match to suggest a candidate to a person, never to approve a row.

What to hold for every row

Read stored values, not a status label. A screen that says "saved" tells you little about which hours are stored.

Check both sides for completeness

Comparing from the source finds shifts that were never saved. It cannot find saved rows that no source supports. Run it in both directions.

Microsoft's full outer join page (2024, an older page) says the join brings in all rows from both tables, including rows that appear in only one, with nulls for the missing side. Its left anti join page (August 2025) says the join returns only the rows from the left table that have no match in the right. Whatever your tool, every unmatched row on either side becomes an exception with an outcome and an owner.

One case escapes. A shift missing from both sides appears in neither list. Catching it needs an expected-population check against a list that is independent of the timesheets and the saved records, such as the bookings or the client's own schedule. Someone has to own that list.

WORKED EXAMPLE - HYPOTHETICAL

A worker covers a morning and an evening shift of equal length at one site on one day. The source holds both. The saved records hold the morning shift twice and no evening shift. The total agrees. A key of worker, date and site flags the genuine pair as duplicates and cannot say which row belongs where. Adding the shift start separates them, and the two-way comparison shows the evening shift missing on the saved side.

Exceptions: who decides, and what must not be the default

Each exception needs evidence, an owner and a decision. The last column names the shortcut that quietly becomes the process.

An unresolved row goes to its owner through your own exception process. The table says nothing about pay, and should not.

Key, approve, change: three different permissions

We suggest three separate permissions, each held by a named person or role where possible. Permission to key writes a value from a valid source into the saved record. Permission to approve confirms that the saved record matches its source. Permission to change an agreed figure alters hours or a rate that someone has already agreed. Holding the first should not carry the other two. Being allowed to key a value is not the same as deciding the source was right.

If one person or tool both keys and approves, the read-back keeps the approval honest. It must read stored values after the write, and a mismatch must stop the approval.

Repeats, corrections and uncertain saves

The same worker, date and site arriving twice could be a repeat or a newer version. Our rule of thumb: the same key with the same content is a repeat, and the same key with different content is a correction that needs the amendment decision above. Microsoft's idempotent consumer pattern (13 August 2026) describes flagging a repeat that carries the same key but different content instead of silently dropping it. Microsoft's Service Bus duplicate detection page (3 June 2026) says a sent message can be committed while its acknowledgement is lost, leaving the sender unsure of the outcome. It says duplicate detection uses an identifier the application sets, within a configured time window, and considers nothing else about the message. That is Azure-specific, but our inference is general: an identifier check cannot tell you whether the content was right.

Microsoft's retry pattern (page review 2024, updated 2025) says a service might process a request but fail to send a response, so a retry can repeat an operation that is not idempotent. A timeout on a timesheet save does not tell you whether it saved. Read the stored values first, then re-key, accept or escalate, and make sure a person and an automatic retry are not both acting on the same row.

Four ways to run the check

Structured inputs make a fixed match easy to inspect, so try an export or integration first. If your systems already expose a supported interface, connect the records your systems already hold and compare there. If one platform has no interface, when a system has no API sets out the routes.

Document reading is where a model earns its place. Microsoft's Document Intelligence documentation (updated 10 July 2026) describes confidence as an estimated probability that an extracted result is correct, says not every field returns one, and says a score can decide whether to accept a result or flag it for human review. That is one product. Our recommendation holds for any tool: the reading is a proposal, and approval stays with a named person or a written rule.

Acceptance cases and weekly ownership

Write the cases a route must pass before relying on it. The general format is in our article on how to define the scope and acceptance checks. The timesheet cases:

• A row matching on key, source and saved values passes as a match.
• A source row absent from the saved records appears as missing from saved, and a saved row with no source as missing from source.
• A repeated key is flagged, whether the content is the same or different.
• A write rejected by the destination is visible with a reason, and nothing downstream treats it as saved.
• An interrupted run records what completed, so nobody retries blind.
• An unreadable source goes to its owner and is not keyed.

For each, name the input, the expected outcome, the evidence to inspect and who accepts it. Use sanitised records and compare without writing first. Two paths making live writes to one record is a different risk from a test comparison.

To judge whether the check helps, record how problems are found today, then repeat the observation after the change, noting dates, sample and method. Set no target first.

One named person owns the weekly check: the batch cut-off, the source evidence, the exception table and the sign-off that the week is closed. They inspect the comparison output and the owners' decisions, not a run status that says the process finished.

Where Clockcheck fits

Clockcheck is our AI worker for timesheets. In its approved description it matches each timesheet to the shift it belongs to, keys the start, finish and break into the scheduling platform, reads the saved values back, and approves only when they match the sheet. It flags apparent duplicates for a person and asks a person in Slack about entries it cannot resolve. If it stops, what it approved stays approved. A person writes the rule for which source wins. This article describes a mechanism and our recommendations. It reports no results.

If you want to test one handoff against this checklist, talk through one timesheet handoff. Bring a sanitised example and the name of the person who owns the check.

No AI worker has run for a client yet. We scope it and discuss it; we do not claim it is running for a client.

Written by

Michael Szalaty Technical director

Delivery, development and infrastructure. Runs a staffing agency.