Getting attendance data off spreadsheets

Nearly every company with an attendance device has the same arrangement. The reader by the door records entries and exits. Once a week or once a month, somebody exports a workbook from it. And then a person spends a day turning that workbook into something the business can use.

The export is not the problem. The day is.

What the export actually contains

Attendance-machine exports are consistently structured and consistently awkward. Typically there is a header block containing the attendance date, then a row of column headings — employee code, name, in time, out time, work duration, total duration — and then one row per employee.

Two details cause most of the pain. First, the file often lies about its own format: a file named .xls is frequently a modern .xlsx workbook, or the reverse, because the device firmware was written against whichever library was to hand. Anything importing these files has to detect the real format rather than trust the extension.

Second, the identifier is an employee code, not a name. Names are unreliable — they are typed differently in two systems, they change, and two people share them. The code is the only thing that joins reliably, which means your employee records have to carry it.

Where the errors come from

In our experience the reconciliation errors are rarely arithmetic. They are these, roughly in order of frequency:

• Somebody did not badge. A forgotten swipe, a door held open, a day working from a client site. The row is missing or half-present, and a human decides what it should have been.
• Leave is in a different file. An absence that was approved looks identical to an absence that was not, until you open the leave sheet.
• Working from home is filed as leave. Which understates attendance and overstates leave consumption at the same time.
• The correction is made in the copy. Somebody fixes a row in their version of the workbook. Next month’s reconciliation starts from the unfixed one.
• New joiners and leavers. Codes that appear mid-month or stop mid-month, which every formula quietly gets wrong.

Notice that only the first is about attendance. The rest are all consequences of the data living in a different place from the leave records, the employee records and the corrections.

What changes when the import is part of the system

The mechanical part — reading the workbook — is the easy half. The valuable half is that the imported rows land next to everything they need to be reconciled against.

In Matrix, the upload reads both .xls and .xlsx, works out which it really is, finds the attendance date and header row, and reads every row whose employee code parses as a number. From there:

• Corrections are made on the record itself, so next month starts from the corrected data rather than the export.
• Approved leave is already in the same system, as records with an approver and a date range — so an absence is either explained or it is not, without opening a second file.
• Work from home is its own request type rather than a leave category, so remote days stop distorting both numbers.
• The employee code belongs to the same employee record that carries the person’s department, reporting line and documents.

How to move without losing history

A practical sequence, learned from doing it badly first:

1. Fix the employee codes before anything else. Every person needs the code the device knows them by. This is the whole migration; everything after it is easy.
2. Set up leave types and holidays to match your current policy exactly. Do not take the opportunity to redesign the policy at the same time. One change at a time, or you will not know which one broke the numbers.
3. Run one month in parallel. Import the export and also do the old reconciliation. Compare. The differences will be genuine data problems you did not know you had.
4. Keep the historical workbooks read-only, and do not import them. Backfilling years of attendance rarely earns its cost. Archive them; start clean.
5. Move the corrections process last. Once the import is trusted, stop maintaining the parallel sheet — otherwise it becomes the shadow system of record.

What it is worth

We will not put a figure on the saving, because it depends entirely on your headcount and how bad the current process is. But the honest measure is not the day of reconciliation. It is the confidence: whether the attendance number in a report is one everybody accepts, or one that always comes with a caveat about the spreadsheet.

If you are also still tracking project hours in a workbook, the same argument applies to that side of the business, and for the same reason: the file is fine, the reconciliation between files is what costs you.