Most organisations that buy a biometric device end up with two attendance systems. The device at the door knows exactly who arrived and when. The attendance system, or payroll, knows the roster and the leave. Between them sits a spreadsheet, exported once a month, cleaned by hand, and argued over on the twenty-eighth. This guide is about removing that spreadsheet.
Why the spreadsheet exists
A fingerprint or card reader records events, not attendance. Each event is a device user id (usually a PIN), a timestamp, and sometimes a direction flag that nobody trusts. Turning a day of events into "present from 09:02 to 17:41 with a 43-minute break" is a set of rules: which punch is an arrival, what to do with a missing punch, how a shift that starts at 22:00 and ends at 06:00 is attributed. Devices do not apply those rules. People do, in a spreadsheet, and every person applies them slightly differently.
The second reason is identity. The device knows PIN 0417. The HR system knows Fatima in radiology. Unless something maps one to the other permanently, the mapping is redone at every export — and the moment a new starter is enrolled on the device with the next free PIN, the spreadsheet is wrong until someone notices.
What a real import does instead
An import that removes the spreadsheet has to do four things, in this order.
- Accept the device export as it is. Every vendor exports a slightly different file. The import should read the export the device produces rather than demand that someone reshapes it first.
- Map each PIN to a person once. The first time a PIN appears, someone picks the employee it belongs to. After that the mapping is remembered, so the second import needs no attention at all. When a new PIN shows up, only that one row asks for a decision.
- Turn events into sessions with explicit rules. First punch of the day is the arrival, last is the departure, punches in between are breaks. A session that crosses midnight belongs to the day it started. A day with a single punch is flagged, not silently counted as a full day or a zero.
- Write the result to the same record everything else uses. Imported sessions appear on the same attendance record as browser clock-ins and leave, so reports, timesheets and approvals do not know or care where a punch came from.
Night shifts are where imports break
Care homes, hospitals, warehouses and 24-hour stores all have shifts that cross midnight. A naive import that closes every session at 23:59 turns one night shift into two half days, one of which has no arrival and one of which has no departure. Payroll then either underpays or a manager corrects it by hand — which is the spreadsheet again, under a different name.
The rule that works is simple to state: a session belongs to the day it started, and stays open until the next departure punch, subject to a cap. The cap matters. Without it, one missed departure leaves a session open for days. Orbiz AMS uses a configurable auto-stop rule so a session that has been open too long is closed and flagged rather than left to grow; we wrote up the reasoning in our notes on attendance, time tracking and timesheets.
Corrections without losing the audit trail
Devices miss punches. Someone forgets to badge out, the sensor rejects a wet finger, a contractor uses the wrong door. A good import treats the imported punch as evidence, not as truth: a manager can add a missing departure or move a break, but the original device event stays visible, and the correction carries who made it and when.
That separation is what makes an audit survivable. When an inspector or an employee disputes a day, you can show the raw device events on one side and the approved session on the other. A spreadsheet that was edited in place cannot show either.
A checklist before your first import
- Export one week of events from the device and look at the raw file. Note the columns you actually have — many devices export no direction flag, which is fine if the import infers arrival and departure from order.
- Print the device user list with PINs. This is your mapping source; check that leavers have been removed from the device.
- Decide the shift rules in writing: what counts as a break, what the cap on an open session is, and how a single-punch day is treated.
- Pick one department for the first import and compare the result with what payroll calculated last month. Differences are usually the spreadsheet being wrong, not the import.
- Only then switch payroll to read from the attendance system. Keep the device export for a month as a fallback — you will not need it, but the person who ran the spreadsheet will sleep better.
Where this matters most
Any team with a device at the door benefits, but two kinds of organisation feel it most. Clinics and hospitals run night shifts across many roles and answer to regulators who want to see the evidence behind a roster. Retail and e-commerce operations hire seasonal staff in bulk, so the "new PIN, who is this?" step happens forty times in a month rather than once a quarter. In both, the employee record the import writes to is the same one HRMS created at hiring, so nobody is entered twice.
Built for your industry
