How do I check payroll hours against timesheets?

Sum timesheet hours per employee and period with SUMIFS, then compare with the payroll export joined on employee ID.

How do I find shifts without a clock-in?

Join the rota to clock-in data on employee and date; rostered shifts with zero clocked hours are missing clock-ins.

What if employees have several clock-ins per day?

Sum them per employee and date before comparing with the rota.

What should I check before each payroll run?

Hours paid against approved timesheets, overtime coding, joiners and leavers against HR dates, and any manual adjustments.

How do I compare hours when timesheets are daily and payroll is monthly?

Sum the daily timesheet hours per employee for the payroll period with SUMIFS, then compare the totals.

How do I spot a forgotten clock-out?

Flag clocked shifts longer than your longest rostered shift, for example more than 12 hours, and check them before payroll closes.

How to reconcile payroll with timesheets

By the Analistable team · Updated 2026-10-07 · 6 min read

Sum approved timesheet hours per employee and pay period, then join them to the payroll export on employee ID. Compare regular hours, overtime and the pay rate. Separately, compare the rota (planned shifts) with clock-in data to find shifts worked but not clocked, or clocked but not rostered, before payroll closes.

Part of our guide: How to reconcile data in Excel

Hours: timesheets vs payroll

Timesheet hours =SUMIFS(Timesheets[Hours], Timesheets[Employee ID], [@[Employee ID]], Timesheets[Week], [@Week])
Difference      =[@[Payroll hours]] - [@[Timesheet hours]]
Pay check       =ROUND([@[Payroll hours]] * [@Rate], 2) - [@[Gross pay]]

Week 38
Employee	Timesheet hours	Payroll hours	Rate	Gross pay	Flag
E-104	37.5	37.5	13.2	495	OK
E-117	42	37.5	12.6	472.5	4.5 hours overtime not paid
E-121	0	20	12.6	252	Paid with no approved timesheet

Shifts: rota vs clock-ins

Join the rota (employee, date, planned start and end) with clock-in data on employee + date:

Clocked hours =SUMIFS(Clock[Hours], Clock[Employee ID], [@[Employee ID]], Clock[Date], [@Date])
Status        =IF([@[Clocked hours]] = 0, "No clock-in",
                IF(ABS([@[Clocked hours]] - [@[Planned hours]]) > 0.25, "Differs from rota", "OK"))

Then the reverse: clock-ins with no rostered shift (COUNTIFS on the rota = 0) are unplanned shifts to approve or reject.

Common causes

Forgotten clock-outs that leave a 16-hour shift.
Shift swaps agreed between staff but not changed in the rota.
Overtime approved by a manager but not coded as overtime in payroll.
Leavers paid for a period after their end date.

For the reverse question — people on the HR list but missing from payroll — use the anti-join check in finding matches between two lists.

Leavers and joiners

Join the payroll export to the HR list of current employees with start and end dates. Anyone paid after their end date, or before their start date, needs checking — both happen when HR changes reach payroll late:

=IF(OR([@[Pay date]] > XLOOKUP([@[Employee ID]], HR[Employee ID], HR[End date], DATE(9999,12,31)),
       [@[Pay date]] < XLOOKUP([@[Employee ID]], HR[Employee ID], HR[Start date], 0)), "Check", "")

Getting the data

Timesheets or time-and-attendance export: employee ID, date, clock-in, clock-out, break minutes and approved hours. Use approved hours where your system has them.
Payroll report: employee ID, pay period, hours paid by type (basic, overtime, holiday), rate and gross pay. Payroll software names these differently, so map them once.
HR list: employee ID, start date, end date, contracted hours and pay rate.

Employee ID is the only reliable key. Names change, are spelt differently between systems, and two people can share one. If a system has no ID, build one mapping table and use it everywhere.

Calculating hours from clock times

Hours        =ROUND(MOD([@[Clock out]] - [@[Clock in]], 1) * 24 - [@[Break mins]] / 60, 2)
Long shift   =IF([@Hours] > 12, "Check clock-out", "")
Missing out  =IF(ISBLANK([@[Clock out]]), "No clock-out", "")

MOD(…, 1) handles shifts that cross midnight: a 22:00 to 06:00 shift gives 8 hours rather than −16. If clock times include the date, simply subtract and multiply by 24. In Google Sheets the same formulas work, provided the times are real time values rather than text.

Second example: valuing unpaid overtime

In week 38, E-117 worked 42.0 approved hours but was paid 37.5. If their contract pays overtime at 1.5 times the basic rate of £12.60:

Overtime owed to E-117
Item	Value
Hours worked − hours paid	42.0 − 37.5 = 4.5
Overtime rate	12.60 × 1.5 = 18.90
Underpayment	4.5 × 18.90 = 85.05

Overtime rules differ by contract, so take the multiplier and any threshold from the employment terms rather than assuming one. Correct the underpayment in the next pay run and note it on the reconciliation, so the following month's comparison isn't thrown out by the back pay.

Troubleshooting

Symptom, cause, fix
Symptom	Likely cause	Fix
Everyone differs by the same amount	Breaks deducted in one system and not the other	Compare paid hours net of unpaid breaks on both sides
Night-shift workers show negative hours	Shift crosses midnight	Use MOD(out − in, 1) × 24
Hours match weekly but not monthly	Pay period and timesheet weeks cut off on different days	Sum timesheets by date range, not by week number
Holiday hours look like unworked pay	Holiday not recorded in the timesheet system	Bring holiday records into the comparison as a separate type
Employee missing from one side	Different IDs or a new starter not set up	Check the ID mapping and HR start dates

Monthly routine

Lock approved timesheets for the pay period before payroll is run.
Run the hours comparison and the rota check; clear every flag with the line manager.
Run the leavers and joiners check against the HR list.
After payroll, compare gross pay per employee with hours × rate and investigate anything outside a small tolerance.
Keep the workbook with the payroll records for the period.

If you also bill clients for staff time, the same timesheet data feeds that reconciliation — see matching timesheet hours to projects.

Doing the comparison across pay periods

If payroll is monthly but timesheets are weekly, don't sum by week number. Sum timesheet hours by date between the pay period's start and end dates:

Timesheet hours =SUMIFS(Timesheets[Hours], Timesheets[Employee ID], [@[Employee ID]],
                        Timesheets[Date], ">=" & [@[Period start]], Timesheets[Date], "<=" & [@[Period end]])

Some payrolls pay hours in arrears — for example, overtime worked in the last week of one month is paid in the next. If so, shift the timesheet date range back by the arrears period, or the comparison will show a difference every month that is entirely expected.

Common mistakes

Comparing scheduled rota hours with payroll, instead of approved worked hours.
Matching on employee names, which breaks when someone changes name or two people share one.
Counting unpaid breaks as paid time on one side only.
Fixing a difference in payroll without fixing the timesheet, so it reappears in next month's comparison.

Keep the flags list short by setting a tolerance, for example a quarter of an hour per shift, so that rounding of clock times doesn't create work.

Checking the result

After clearing the flags, total timesheet hours for the period should equal total paid hours, minus documented differences such as holiday, sick pay or arrears adjustments. Gross pay recalculated from hours and rates should be within pennies of the payroll report for every employee. Anyone outside that tolerance has a rate, a pay element or an adjustment the workbook doesn't know about yet.

Agency and contract workers

Workers paid through an agency don't appear in payroll, but the agency invoice is based on hours too. Compare the hours on each agency invoice with your own timesheets for those workers using the same SUMIFS by date range, before the invoice is approved.

Frequently asked questions

How do I check payroll hours against timesheets?: Sum timesheet hours per employee and period with SUMIFS, then compare with the payroll export joined on employee ID.
How do I find shifts without a clock-in?: Join the rota to clock-in data on employee and date; rostered shifts with zero clocked hours are missing clock-ins.
What if employees have several clock-ins per day?: Sum them per employee and date before comparing with the rota.
What should I check before each payroll run?: Hours paid against approved timesheets, overtime coding, joiners and leavers against HR dates, and any manual adjustments.
How do I compare hours when timesheets are daily and payroll is monthly?: Sum the daily timesheet hours per employee for the payroll period with SUMIFS, then compare the totals.
How do I spot a forgotten clock-out?: Flag clocked shifts longer than your longest rostered shift, for example more than 12 hours, and check them before payroll closes.