Reconciling timesheets, leave and payroll automatically

Most Australian businesses under a few hundred staff run payroll across at least three systems that never talk properly: a form or email where leave gets requested, a timesheet system where hours get captured, and an accounting package like Xero where the pay run actually happens. The glue between them is usually a person with a spreadsheet. Practitioners in hospitality, retail, healthcare and manufacturing describe exactly this setup, a patchwork of spreadsheets, manual timesheets and CSV exports where the software looks free but the real cost turns up as hidden hours fixing the output.

This post is part of our Practical AI in Business Operations series, and it covers one of the least glamorous things we build for ourselves and for clients: scheduled checks that compare two systems which should agree, and send an email when they don’t.

The pattern

Everything below is the same three moving parts.

• A source of truth that a human approved: a signed leave form, an approved timesheet, a contracted monthly hour cap.
• A scheduled task, usually daily or on a payroll cycle, that pulls the corresponding records out of the operational system (Xero, the time capture tool, the project register) and compares them.
• An email to a named person listing only the rows that disagree, with enough context to act on without opening three tabs.

The important detail is the first one. Reconciliation checks are worth far more when they are anchored to a stored reference record rather than to the second system’s own output. In an open-source HR system, checking a front end against the saved API examples for roles, project assignments and project lists surfaced three integration bugs nobody had reported, including assignee names rendering as raw GUIDs and a paginated response shape silently producing an empty project list. The saved examples were the reference. The payroll equivalent is the approved leave form, not whatever the timesheet system decided to export.

Three checks worth building first

Leave forms received versus leave entered in payroll. Someone submits a leave request. It gets approved. Then it has to be keyed into Xero as a leave application against the right leave type, for the right dates, at the right hours. The check reads approved requests for the period, reads leave applications from the Xero API, matches on employee and date range, and reports anything present on one side and missing on the other, plus anything where the hours differ.

A daily timesheet summary email. Not a dashboard. An email, each morning, listing who logged hours yesterday, who didn’t, and the totals per person. This is a cheap way to catch the failure mode where someone stops entering time for a fortnight and nobody notices until invoicing.

Monthly client-hour caps. Where a retainer or a contract caps hours per month, the check joins time entries to project and client assignments and flags anyone tracking toward the cap. That join is its own source of error: who is assigned to which project or client lives in a different system from time capture, and mismatches between assignment data and time data are a known bug class. If the assignment table is stale, the cap report is wrong in a way that looks perfectly plausible.

Most of this is a join, not AI

Be honest about how much of the work needs a model. For structured comparison on clean keys, a plain query is better in every dimension that matters: it is deterministic, it is testable, it costs nothing to run, and when it produces a wrong answer you can read the code and see why.

A leave reconciliation in C# against a Xero pull looks roughly like this:

var missingInPayroll = approvedRequests
    .Where(r => r.StartDate >= periodStart && r.EndDate <= periodEnd)
    .Where(r => !xeroLeave.Any(x =>
        x.EmployeeId == r.EmployeeId &&
        x.LeaveTypeId == r.LeaveTypeId &&
        DatesOverlap(x, r)))
    .ToList();

var hoursMismatch = approvedRequests
    .Join(xeroLeave, r => r.Key, x => x.Key, (r, x) => new { r, x })
    .Where(p => Math.Abs(p.r.Hours - p.x.Hours) > 0.01m)
    .ToList();

if (missingInPayroll.Any() || hoursMismatch.Any())
    await email.SendVarianceReport(missingInPayroll, hoursMismatch);

That is the whole idea. A scheduled job, two queries, a comparison, a conditional email. We run this style of check on our own internal systems, and the pattern transfers to any pair of systems that should agree: bank feed versus invoice ledger, roster versus timesheet, CRM versus billing.

Where an LLM actually earns its place

Two spots, both narrow.

The first is free-text leave reasons. People write “carers day for my son”, “sick - flu”, “medical appt Thursday am”, or nothing at all. Payroll needs a leave type. A model is genuinely good at mapping unstructured text to a fixed set of categories, and it costs almost nothing because you only send the handful of rows that failed the deterministic match. We treat the output as a suggestion attached to the variance email, never as an automatic write into payroll. If the classification is clinically adjacent, personal or carer’s leave attached to a medical reason, the design requirement is that a human approves the categorisation before it touches a record, and the model output is stored as a suggestion with the approver’s name against the final value.

The second is name and identifier variants. “Rob Nguyen” in the timesheet tool, “Robert Nguyen” in Xero, “R. Nguyen (contractor)” in the project register. Fuzzy string matching handles a lot of this, but it fails on nicknames, married names, transliterations and the person who joined as a contractor and later became an employee with a new ID. A model with the candidate list in context resolves these well, and you can cache the resolved mapping so you pay for each variant once.

What we do not do is ask a model to decide whether the numbers agree. Arithmetic and set comparison belong in code.

Variances are usually definitional

Once these checks run, the first month produces a pile of mismatches, and the instinct is to treat every one as a data entry error. Usually it isn’t. When two sources measure the same population and disagree, the difference is often in the definitions rather than the data. The New York Fed’s August 2026 analysis of diverging credit card delinquency measures is a good illustration: two delinquency series told opposite stories about American households, and the gap came down to whether charged-off debts stayed in the numerator, not to bad reporting by anyone.

Payroll has the same shape. A public holiday inside a leave period, a half day counted as 4 hours in one system and 3.8 in another, leave accrued on a roster cycle versus a calendar month. Each of those produces a permanent variance that a rule can express once. Budget real time in the first cycle to classify variances into “genuine error” and “definitional”, then encode the definitional ones as tolerances or exclusions. If you skip that step, the daily email becomes noise and people stop reading it, which is worse than not having the check at all.

Costs and limitations

Build cost for a single check is small, typically a few days including the API plumbing and the first round of variance triage. Running cost is a scheduled job and a mailbox. Where the money actually goes is maintenance: API contracts change, leave types get added, someone renames a project. The build versus buy decision in payroll integration is usually decided by ongoing maintenance and error correction effort rather than licence cost, and that applies just as much to checks you write yourself.

The limitations are worth stating plainly. These checks detect disagreement; they do not fix it, and we deliberately keep them read-only against payroll. They cannot catch an error that both systems share, such as an incorrect award rate applied consistently everywhere. And they need an owner. An unowned variance email is a filtered folder within a month.

None of this is exciting work. It is, however, the kind of automation that pays for itself in the first quarter, keeps your records defensible under Fair Work obligations, and stops errors flowing into Single Touch Payroll reporting where they are much more annoying to correct.

PicNet builds production AI systems for Australian organisations. Talk to us about what a first project could look like.

Tagged: #payroll #xero #data-integration #automation #back-office