Articles ·
From Excel to Excel: comparing fleet journeys with working hours
A company's weekly fleet report is compared with tracked work hours using fixed rules. Why a language model should not decide when the report is about people's time.
In most companies, Excel is the common language. Payroll asks for data in Excel, the fleet-tracking provider sends its report as an Excel file, and clients want to see how many hours their project took in Excel. A time-tracking system cannot be an island: it needs to read and produce these spreadsheets.
The request
One Monday morning, the office sent over a spreadsheet: the fleet provider’s weekly report for the company’s vehicles. Each car had its own worksheet, listing every trip with departure and arrival times, addresses, mileage and idle time. The request was to compare it with employees’ working hours and flag anything that did not line up.
I agreed to do it on one condition, which the manager shared: it had to use proper data processing and clear logic, not guesswork.
Why not let AI decide?
AI helped me build this program; I used it throughout development. But I would not let it decide whether a row counts as a discrepancy. If a report says whether someone was where their work schedule expected them to be, every line must be explainable. A language model can sound convincing, but it may not give the same answer twice, and its reasoning cannot always be traced back to the source data row by row.
When a report concerns people’s working hours, every line must be traceable.
That is why the importer is strict. It classifies each row by its format—trip, daily total, grand total or idle time. If it does not recognise something, it stops and tells me which worksheet and row need attention. It also checks the imported trips against the spreadsheet’s own daily and weekly totals. If the mileage does not add up, it does not continue.
The rules
The comparison uses seven fixed rules and thresholds. In plain English, it flags when:
- a car moves on a day when its driver has no tracked working hours, for example because they are on leave;
- a car stays at a worksite for at least ten minutes, but its driver’s phone is not there;
- the phone says the employee is at a site while the car has been travelling for more than fifteen minutes or is somewhere else;
- the phone records onsite work, but the car has not moved that day;
- none of the KlipTrack users can be matched to the car’s trips that day;
- the driver’s phone travels in a different car that day;
- another colleague’s phone, rather than the driver’s, follows the car throughout the day.
Each flag says which rule triggered it, how many minutes are involved and which row of the source spreadsheet it came from.
Who was driving?
The first obstacle was that the report did not say who had driven. The worksheet names were just a licence plate and a nickname. I did not guess. The office identified the cars, and where one was shared, the data helped: when it was parked at a worksite, I checked whose phone was at the same place at the same time. The person whose phone matched at least half the stops was treated as the driver that day. If it was not clear, the report says so instead of making up a name.
I converted addresses to coordinates using OpenStreetMap, so the comparison can account for a car parked around the corner instead of at the exact site address. The geocoding result is saved, so the weekly run does not look up the same addresses again.
What did the first week show?
The main finding was that tracked working hours and vehicle movements did not contradict each other. Where both sources had data, they matched minute by minute. What we did find was about vehicle use: someone made a longer journey in one car without having the app; another car moved on a day off.
One moment became a lesson. In one case, a car was parked near a site for two hours while the phone recorded no presence there. My first thought was that the site’s radius was too small. Before saying so, I checked where the phone actually was: several hundred metres away, by a different building. The radius was fine; my assumption was not. Every conclusion needs to be backed by data.
What does the client receive?
There are two versions. The internal report includes every trip and stop. The client version includes only the rows that need clarification, highlighted in yellow with a short explanation. For stops that look like home addresses, it shows only the town or Budapest district. The manager needs to see discrepancies, not where an employee lives.
The comparison has since become a weekly routine: the report arrives, the importer runs, and a concise list goes back.
From Excel back to Excel
The same thinking goes into KlipTrack’s own exports. The time sheet includes signature columns and subtotals per employee; the payroll file has one row per person; the travel report lists journeys between worksites. They all come from the same tracked data shown in the office workspace, so the spreadsheet and the screen do not disagree.
A reliable import and export can be more useful to a company than another screen. Excel can stay. The job is to make sure the right data gets into it.
In the next part, I explain why almost everything in the system can be switched off: Features, not tiers.