The shape of the problem
A scan log is an event stream. Each row says a code was presented at a time, and if you are using two scanner links, which direction it was. Payroll and reporting want something different: one row per person per day with a total. Getting from one to the other is a pairing problem, and it is entirely doable in Google Sheets with three formulas.
The worked examples below assume a raw tab named Log with the timestamp in column A, the scanned code in column B, and the scanner link name in column C. Adjust the ranges to match your own sheet.
Step one: get the pairs
The most reliable pairing formula finds, for a given clock-in row, the smallest clock-out timestamp for the same code that is later than the clock-in. MINIFS does exactly that in one pass, and it does not care how many rows sit between the two.
- Matching clock-out: =MINIFS(Log!$A:$A, Log!$B:$B, $B2, Log!$C:$C, "Clock out", Log!$A:$A, ">"&$A2)
- Hours worked: =IF(D2=0, "", (D2-$A2)*24) where D2 holds the matching clock-out from the formula above
- Rounded to two decimals for reporting: =IF(D2=0, "", ROUND((D2-$A2)*24, 2))
MINIFS returns 0 when nothing matches, which is why every formula above tests for zero first. That zero is not a mistake to hide; it is the missed clock-out, and the next section deals with it properly.
Step two: handle the missed clock-out
Someone will forget. This is the single most common failure in every timekeeping system ever built, and a spreadsheet handles it badly by default because an empty cell quietly totals as nothing.
Give the unpaired rows their own visible list rather than burying them. A filter over the calculation tab showing every clock-in whose matching clock-out is zero gives you the exceptions to chase, and it takes one formula.
- List the exceptions: =FILTER({Log!A2:A, Log!B2:B}, Log!C2:C="Clock in", COUNTIFS(Log!B:B, Log!B2:B, Log!C:C, "Clock out", Log!A:A, ">"&Log!A2:A)=0)
- Count them, so the number is visible on the summary: =COUNTA(...) over that filtered range
Never fix a missed clock-out by typing a plausible time into the raw log. Record the correction on a separate corrections tab with who made it, when, and why. An employer editing time records downward with no reason and no acknowledgement is exactly the pattern that loses a wage dispute, and Google Sheets version history makes every edit discoverable.
Step three: overnight shifts
A shift from 22:00 to 06:00 breaks any formula that compares times without dates. Subtracting 06:00 from 22:00 gives a negative number, and a naive sheet shows minus sixteen hours.
The fix is to keep the full datetime rather than the time of day. If your timestamp column carries the date, the arithmetic already works and an overnight shift needs no special case at all. This is the main practical reason to write a full YYYY-MM-DD HH:MM:SS value into the sheet rather than a bare time, and it is why the scanner writes the full datetime by default.
Set your workspace timezone in Settings before the first shift. Timestamps default to UTC, and a night shift logged in the wrong zone lands on the wrong calendar day, which quietly moves hours between weeks.
Step four: total per person per day
Payroll and HR systems want one row per person per day with a total, not a stream of events. A QUERY over the calculation tab produces exactly that, and it is the tab you export rather than the raw log.
- Daily totals: =QUERY(Calc!A:E, "select B, toDate(A), sum(E) where E is not null group by B, toDate(A) label sum(E) 'hours'", 1)
- Weekly total per person: wrap the same query in a SUMIFS over a date range, or add a week-number column and group by it
- Export that tab as CSV, which is what most payroll and HR systems accept
What this does not give you
These formulas produce durations. They do not produce a compliant timesheet, and the distinction is worth stating plainly before anyone runs payroll from the output.
- No break rules. Paid and unpaid break handling, and any legally required meal period, is not modelled here.
- No overtime logic. Overtime under the US Fair Labor Standards Act is computed over a fixed recurring 168-hour workweek that need not match the calendar week, and it cannot be averaged across weeks.
- No rounding policy, deliberately. Rounding is legally constrained, and where exact time is captured the trend in case law is that exact time must be paid.
- No approval step. There is no supervisor sign-off, no locked pay period and no audit trail of who changed what.
- No proof of identity. A scan shows a code was presented, not who presented it.
Written by QR to Sheets. We build the scanning half, not a payroll system, and the formulas above work on any timestamped log regardless of what produced it. If you need automatic hours, overtime and an approval workflow as a product feature, a purpose-built time and attendance system is the right purchase.
Getting the scans in there in the first place
Everything above assumes rows are already arriving. The free way to produce them is a Google Form with an In and Out dropdown, whose responses land in a spreadsheet with a timestamp. The faster way, once typing names at a shift change becomes the bottleneck, is two scanner links that write rows directly. The companion guide on QR code clock in and clock out covers that setup.
Step by step
- 1
Keep the raw log on its own tab and never edit it
Scans append to one tab in arrival order. Treat that tab as read-only: every calculation below reads from it and writes somewhere else. An edited raw log is worthless as evidence and impossible to reconcile later.
- 2
Make sure the timestamp is a real datetime, not text
A cell showing 2026-09-11 08:03:12 that is left-aligned is text, and arithmetic on it silently fails. Select the column, use Format then Number then Date time, and confirm it right-aligns. Rows written by QR to Sheets already arrive as YYYY-MM-DD HH:MM:SS, which Sheets parses as a datetime.
- 3
Sort by person, then by time
Pairing only works on an ordered list. Sort by the scanned value (the person or badge code) and then by timestamp ascending, so each clock-in is immediately followed by its own clock-out.
- 4
Pair each in with the next out
For a row on the Clock in link, find the earliest Clock out row for the same code with a later timestamp. That is the matching pair, and the difference between the two timestamps is the duration.
- 5
Convert the difference to decimal hours
Subtracting two datetimes gives a fraction of a day. Multiply by 24 for decimal hours, which is the shape payroll systems accept. Format the result as a plain number, not as a duration, or it will display as a time of day.
- 6
Flag anything that did not pair
An unmatched clock-in means someone forgot to scan out. Surface those rows in their own list rather than letting them total as zero, because a silent zero is how an underpayment happens.
Frequently asked questions
Why does my hours column show a time of day instead of a number?+
The cell is formatted as a duration or a time. Subtracting two datetimes gives a fraction of a day, so multiply by 24 and format the result as a plain number with two decimal places. If it still shows a clock time, select the column and set Format, Number, Number.
How do I handle a shift that crosses midnight?+
Keep the full date and time in the timestamp rather than the time alone. With the date included, the subtraction works across midnight with no special case. A negative duration is the symptom of comparing bare times.
What should I do about someone who forgot to clock out?+
Surface it as an exception rather than letting it total as zero. Filter for clock-ins that have no later clock-out and chase those specifically. Record any correction on a separate tab with who changed it and why, and never retype the raw log.
Can I use this for payroll?+
The formulas produce durations, which may be one input to payroll, but the log itself is not a compliant time record. It has no break rules, no overtime calculation, no approval workflow and no protection against editing. In the United States the Fair Labor Standards Act requires the employer to keep hours worked each day and each workweek, and where the employer's records are inadequate the burden of proof shifts to the employer. Take proper advice before running payroll from a spreadsheet anyone can edit.
Do the formulas work if I only have one scanner link?+
Not for pairing, because nothing distinguishes an arrival from a departure. Two links, one named Clock in and one named Clock out, write the direction into each row and make the pairing unambiguous. With a single link you would have to infer direction from odd and even scan order, which fails the first time someone scans twice.