How to track tardies in Google Sheets: a late sign-in log the front office can scan

Most front offices still log late arrivals on paper: a tardy sheet on the counter where students write their name, the time and a reason, and a slip they carry to class. It records the morning well enough. The trouble starts later, when someone has to count how many times each student was late this term, read handwriting that is half a name, and type it all into a spreadsheet before a meeting.

This guide covers the free Google Form route in full, then where it breaks, then a setup where office staff scan each late student's card into a Google Sheet. It ends with the formulas that turn the log into tardies per student, a flag for repeat lateness and a list of who was late today.

What should a tardy log record?

One row per late arrival, holding the student's ID and the time they reached the office. Names live once, on a Roster tab, and formulas look them up, so a misspelled name never splits one student into two. If your school separates excused from unexcused lateness, record that too, either as a separate link or as a column filled in when the note arrives.

Be clear about which lateness you are counting. A front office log records students who arrive late to school. Lateness to a later period happens at the classroom door, and the teacher's own register catches it; the guide to daily classroom attendance compares each scan with the period start time.

What is the simplest free way to log tardies in Google Sheets?

A Google Form. Make a form with one required short-answer question, Student ID, and optionally a Reason dropdown. In the form's Responses tab, link it to a spreadsheet; Google's help describes this as storing the responses in a linked Google Sheet, and every submission arrives as a row with a Timestamp column that Google Sheets holds as a real date. Print the form's QR code and tape it to the office counter, or keep the form open on an office tablet.

• Stop most typos before they reach the sheet: on the Student ID question, turn on Response validation, choose Regular expression and Matches, and enter a pattern such as ^S[0-9]{4}$ for IDs like S1042. Google's help lists Regular expression as a rule for short-answer questions.
• Add a Tally tab. Type the first day of term in B1, put Tally headers in row 2, and list the roster from A3 with =FILTER(Roster!A2:B, Roster!A2:A<>""), which fills the IDs into column A and the names into column B.
• Count late submissions per student in C3, then fill down: =COUNTIFS('Form Responses 1'!B:B, $A3, 'Form Responses 1'!A:A, ">="&$B$1). This assumes the Student ID question is the form's first question, so its answers are in column B.
• That formula counts submissions, so a student who submits twice in one morning counts twice. The late-days formula further down counts each day once and works on form responses too.

Where does a Google Form tardy log break?

• Typed IDs drift. S1042, S 1042 and 1042 are three different answers to a formula, and a validation pattern only catches the ones that do not fit the shape. A wrong but well-formed ID becomes a tardy for somebody else.
• Anyone with the link can submit from anywhere: from the hallway, from the bus, or for a friend who has not arrived yet. Every response looks the same.
• Students need a phone with signal, or they queue at the office tablet while the bell has already gone.
• The office ends up watching the form anyway, to check that the student in front of them actually submitted.

The other free route keeps the typing out of it: a USB barcode scanner on the office computer, scanning the barcode on each ID card straight into the spreadsheet, with a short Apps Script that adds the time. It suits one desk well. The sheet has to stay open on that computer, and whoever runs the desk needs edit access to the whole spreadsheet. The USB barcode scanner timestamp guide has the script and its limits. A USB scanner types into the spreadsheet itself, not into the scanner link described next.

Can the front office scan a student ID card instead?

Yes, and this is the part QR to Sheets sells. An administrator signs in with Google once, connects the school's Google Sheet, and creates a scanner link named Late that writes to a tab called Log. Office staff open that link in the office phone's browser, allow the camera once, and scan each late student's card as they arrive. There is no app and no account for the person scanning, and they never get access to the spreadsheet.

Each scan adds one row to the Log tab: the scan time in column A, the card's value in column B and the link in column C, written as link:Late. Columns D, E and F hold the workspace name, the device type and a scan ID.

• Cards the school already has: the scanner reads QR codes (including Micro QR), Data Matrix, Aztec, PDF417, Code 128, Code 39, Code 93, EAN-13, EAN-8, UPC-A, UPC-E, ITF (including ITF-14) and GS1 DataBar. It does not read Codabar. Scanning existing student ID cards covers checking what a card actually encodes.
• No cards, or cards with no code: print a QR card per student that holds only the ID, with the free QR code ID card maker. It needs no signup and makes the cards in your browser.
• A forgotten card is not a problem: the scanner has a manual entry field, so staff type the ID and the row is written the same way. Keep a printed list of IDs at the desk.
• The time is written as text in the form YYYY-MM-DD HH:MM:SS, in the timezone set for your workspace, and Google Sheets stores it as text. The formulas below convert it with VALUE(). Set the workspace timezone in Settings, and the spreadsheet's own time zone under File, Settings, to the same zone.
• If the Log tab is empty when scanning starts, the first scan lands in row 1. Type the six column names, scanned_at, qr_value, scanner_email, organization, device_type and scan_id, into row 1 first, so every formula can start at row 2.

Each phone that scans counts as one device. Free covers one phone; Premium is $7 per phone per month. People being scanned never open the link and need nothing. One office phone or tablet is one device however many students it scans.

If the signal drops, the open scanner keeps saving scans on the phone and sends them when the signal is back, each with its original scan time. Those rows can land lower in the sheet than later ones, which is why the formulas below go by the time in each row, not by row order.

A card scanned twice on the same link within 12 hours shows an amber Already scanned notice on the phone, and the second row is saved anyway; the formulas count late days, not rows. Keep the link's Accept duplicate scans setting on. Switched off, a link records each code once, ever, so it would refuse a student's second tardy of the year.

How do I count tardies per student this term?

On the Tally tab from the Google Form section, with the first day of term typed in B1 and the roster listed from A3, put this in C3 and fill it down as far as the roster goes:

• Late days since B1: =IF($A3="", "", IFERROR(ROWS(UNIQUE(FILTER(IFERROR(INT(VALUE(Log!$A$2:$A)), 0), Log!$B$2:$B=$A3, IFERROR(VALUE(Log!$A$2:$A), 0)>=$B$1))), 0))
• How it works: VALUE() turns each text timestamp into a date and time, INT() keeps the day, and UNIQUE counts each day once, so a card scanned twice in one morning is one tardy. When a student has no rows, FILTER finds nothing and IFERROR shows 0.
• VALUE() leaves a real date as it is, so the same formula works on form responses: replace Log!$A$2:$A with 'Form Responses 1'!$A$2:$A and Log!$B$2:$B with 'Form Responses 1'!$B$2:$B.
• For one month or a reporting period, type the end date in C1 and use: =IF($A3="", "", IFERROR(ROWS(UNIQUE(FILTER(IFERROR(INT(VALUE(Log!$A$2:$A)), 0), Log!$B$2:$B=$A3, IFERROR(VALUE(Log!$A$2:$A), 0)>=$B$1, IFERROR(VALUE(Log!$A$2:$A), 0)<$C$1+1))), 0))
• If excused arrivals are scanned on a second link named Excused late, count only the Late link: =IF($A3="", "", IFERROR(ROWS(UNIQUE(FILTER(IFERROR(INT(VALUE(Log!$A$2:$A)), 0), Log!$B$2:$B=$A3, Log!$C$2:$C="link:Late", IFERROR(VALUE(Log!$A$2:$A), 0)>=$B$1))), 0))
• IDs must match exactly. A scanned 1042 is text, a 1042 typed on the Roster tab is a number, and the two never match. Give IDs a letter prefix, or format the Roster's column A as plain text before typing. The guide to showing the name for a scanned ID, in the related guides below, covers this and the lookups that put names beside IDs.

How do I flag the third tardy?

• Flag, in D3, filled down: =IF($C3="", "", IF($C3>=3, "3 or more: follow up", "")). Change the 3 to whatever number your policy uses.
• To colour the whole row, select A3:D on the Tally tab, open Format, Conditional formatting, choose Custom formula is, and enter =$C3>=3.
• Sort the tab by column C, or filter it on column D, before each weekly review.

The sheet flags; it does not act. Neither the formula nor QR to Sheets sends a warning letter, writes a detention slip or messages a parent. If you want those generated for you, that is what dedicated tardy tools do. TardEase, a Google Workspace Marketplace app, says on its listing: simply input their student ID number in a Google Form, and the system will track when the student was late. The same listing says the second tardy might generate a warning letter that will need to be signed by parents and the third tardy would generate an administrative detention. Its listing shows pricing as Not available.

Who was late today, and by how many minutes?

A Today tab gives the office a list for the morning. Put =TODAY() in B1, or type a date to look back, and type the bell time in B2, such as 8:30. Put the headers ID, Arrived, Minutes late and Name in row 4, then:

• ID, in A5: =IFERROR(UNIQUE(FILTER(Log!B2:B, IFERROR(INT(VALUE(Log!A2:A)), 0)=$B$1)), "") lists each student scanned on that date once.
• Arrived, in B5, filled down: =IF(A5="", "", MIN(FILTER(IFERROR(MOD(VALUE(Log!$A$2:$A), 1), 0), Log!$B$2:$B=A5, IFERROR(INT(VALUE(Log!$A$2:$A)), 0)=$B$1))). Format column B as Format, Number, Time.
• Minutes late, in C5, filled down: =IF(A5="", "", ROUND((B5-$B$2)*1440))
• Name, in D5, filled down: =IFERROR(VLOOKUP(A5, Roster!A:B, 2, FALSE), "Not on the roster")

MIN takes the earliest scan that day by time, not by position in the sheet, so a scan that reached the sheet late still shows the right arrival time. Minutes late are measured from the bell time in B2. They are a duration between two times, not a judgement about whether the lateness was excused.

Is this late to school or late to class?

Late to school. SwipeK12, which sells school attendance software, makes the point in its article on tracking tardiness: a clipboard at the front office may document some late arrivals, but it cannot reliably show whether students arrived late to second, fifth, or seventh period. The same is true of a front office scan. Per-period lateness needs a register at each classroom door, which is the daily classroom guide linked above, or a dedicated system if your school wants every period tracked centrally.

What about the late slip?

The scan row is the record. QR to Sheets does not print slips. If your school hands late students a slip to show their teacher, keep doing that. If you would rather hand out a reusable laminated pass than print a slip each time, the free hall pass generator prints card-size passes for whatever destinations you type, so a set labelled Late to class is one line.

Which fits: a form, a sheet you scan into, or a tardy system?

Dedicated tools do more than a sheet, and if you need what they do you should buy one. As of 2026-10-12, OneTap's article on tardy tracking says the best way to handle tardies is to let students check themselves in using a simple digital system: a student scans a QR code or taps a screen, on a tablet at the front desk or on their own device. OneTap's pricing page lists a free plan with up to 20 profiles, and a Basic plan at 49.99 USD per month billed yearly with up to 250 profiles.

Where each one wins. TardEase wins if you want consequence letters produced for you from a form you already run. OneTap wins if you want students to check themselves in at a kiosk and reports without building formulas. A Google Form wins on cost when students reliably type their own IDs. QR to Sheets fits an office that wants staff to scan the cards students already carry, with the record in the school's own Google Sheet and no app for anyone.

Student data in a tardy log

• Put an ID in the QR code, never a name or a date of birth. Any phone camera can read a QR code, so a lost card should show a number and nothing else.
• Keep the spreadsheet in a school-controlled Google account, shared only with the staff who follow up on tardies. Office staff who scan never get access to the spreadsheet.
• A scan proves a code was presented, not who was holding it. Staff scanning each card with the student in front of them is what makes the record believable; whether QR attendance can stop buddy punching explains the limit.
• Decide how long tardy records are kept, under your school's own records policy, and clear old rows on that schedule.

Step by step

1. 1

   Make a Roster tab

   One row per student: the ID in column A, the name in column B and the homeroom in column C. Format column A as plain text before typing, so an ID such as 01042 keeps its leading zero.

2. 2

   Choose how a late arrival is recorded

   Either a Google Form with a required Student ID question, or a scanner link named Late that office staff use to scan each late student's card. Both give one timestamped row per late arrival.

3. 3

   Put the record at the front office

   Keep one phone or tablet at the desk where late students sign in. For a form, show its QR code on the counter; for scanning, open the scanner link on the office phone.

4. 4

   Add a Tally tab

   Type the first day of term in B1, list the roster in column A, and add the late-days formula in column C and the flag in column D. Each student's count updates as rows arrive.

5. 5

   Review the flags each week

   Sort or filter the Tally tab by the flag column and follow up under your school's own tardy policy. The sheet counts and flags; people decide what happens next.

Frequently asked questions