All guides
Time and presence

How to calculate hours worked from scan rows in Google Sheets

Turn clock-in and clock-out scan rows into hours per person per day with real Google Sheets formulas, including overnight shifts and missed clock-outs.

By the QR to Sheets team 9 min readUpdated

Short answer

To calculate hours worked from scan rows in Google Sheets, pair each clock-in with the next clock-out for the same person using MINIFS, then subtract the two timestamps and multiply by 24 for decimal hours. A QUERY then totals those durations per person per day.

Two cases break a naive subtraction: a shift crossing midnight, which needs the date included in the timestamp rather than the time alone, and a missed clock-out, which leaves an unpaired row that must be flagged rather than silently ignored.

Build every calculation on a separate tab and leave the raw scan log untouched, so the record of what actually happened is never overwritten by a formula.

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 same pairing gives minutes present per session for CPD and training attendance. For unpaid shifts, tracking volunteer hours adds monthly totals per volunteer and a corrections tab for missed sign-outs.

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. QR to Sheets writes that column as link: followed by the link name, so a row from the Clock out link reads link:Clock out, and the formulas match on that. Leave Log alone and work on a second tab named Calc. In Calc!A2 put =FILTER({ARRAYFORMULA(IFERROR(VALUE(Log!A2:A), "")), Log!B2:C}, Log!A2:A<>""): it copies every row with the timestamp converted to a real date-time, and it grows as new scans arrive. Format Calc column A as Format, Number, Date time, so the daily-totals QUERY further down treats it as a date. Calc then holds the time in A, the code in B and the link name in C, and the working columns go in D and E. 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, in Calc!D2 beside a clock-in row and filled down: =MINIFS($A:$A, $B:$B, $B2, $C:$C, "link:Clock out", $A:$A, ">"&$A2)
  • Hours worked, in Calc!E2: =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({Calc!A2:A, Calc!B2:B}, Calc!C2:C="link:Clock in", COUNTIFS(Calc!B:B, Calc!B2:B, Calc!C:C, "link:Clock out", Calc!A:A, ">"&Calc!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. For a site gate with a rotating crew, QR site sign-in shows where the stations go.

Step by step

  1. 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. 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. Reformatting the column does not convert text that is already there; VALUE() does. Rows written by QR to Sheets arrive as YYYY-MM-DD HH:MM:SS in your workspace timezone, which VALUE() turns into a date-time, so the Calc tab below converts every timestamp once and the formulas never depend on how the cell is stored.

  3. 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. 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. 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. 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.

Try it on your own Google Sheet

Connect Google, create a scanner link, and open it on the phone that will scan. Have that phone at hand: setup takes a few minutes and ends with your first row in the Sheet. Free covers 300 scans on one phone, and the people you scan never install anything.

Start for free

No credit card required

A second door, booth or classroom is a second phone

Free covers one phone and one scanner link. Premium lets several phones scan at the same time, one per door, booth or classroom, into the same Sheet: $7 per phone per month. Running several classes, events or lists from one phone? Premium on that one phone is $7 a month, with a separate scanner link for each. Paid yearly, it is $70 per phone per year (2 months free).

How many phones will scan at the same time?

$7 x 2 = $14 a month

Sign in with Google and, on the free plan, the billing page opens with this choice. Nothing is charged until you confirm the card checkout, and you can change the number of phones later.

Keep reading