Leave balances brought in from a spreadsheet: what to check before loading

Echipa HR 365 · reviewed 2026-09-04 · 5 min read

Importing balances is not a technical operation, it is a decision: you fix an anchor date, declare each person’s balance at that date, and from there the system calculates on its own. What came before stays as history and is not recalculated — any other approach produces figures that contradict what people have already been told.

The anchor date: the choice that simplifies everything else

The anchor date is the day from which the system becomes the source of truth. The best choice is the first day of a month, ideally one when nobody is on leave — the start of the year or a winter month. The worst choice is “today”, because today half the team has periods in progress.

The rule that avoids the longest possible argument: the balance at the anchor date is communicated to each person in writing, before the import, with a deadline for raising objections. Corrected beforehand, it is a correction. Corrected in December, it is a dispute.

The nine checks

Each done on the file, before the import

1. 1 — Every row has a unique, stable identifier — a code, not a name. Two identical names break any import.
2. 2 — There are no duplicate rows for the same person. Checked by code, not by eye.
3. 3 — Balances are numbers, not text. A cell reading “21 days”, or with a trailing space, imports as zero or blocks the row.
4. 4 — Part days follow the same convention throughout: 0.5 or 4 hours, not both in the same file.
5. 5 — Nobody has an unexplained negative balance. If they have days taken in advance, those sit in a separate column.
6. 6 — Anybody with a different individual entitlement is marked as such, with their value — not left on the general formula.
7. 7 — People who have left the company are not in the file. A balance imported for somebody who is gone produces pointless monthly alerts.
8. 8 — People hired after the anchor date are not in the file. Their balance is calculated by the system, from their start date.
9. 9 — The sum of balances is compared with the total from the old records. A one-day difference means a wrong row somewhere.

What you do with mismatches

There will be some. In a 150-row file maintained by hand for a few years, between five and fifteen rows will not match what the person believes. The order of resolution matters:

The rule in the first and third rows — in case of doubt, the person wins — looks expensive and is not. At the scale of a 150-person company, the difference is a few dozen days, once. The alternative is to begin the relationship with the new system through a dispute you cannot prove.

What you do immediately after the import

1. Check three to five people at random against the file: the balance, the individual entitlement, the days already scheduled.
2. Check the special cases you know about: who has a different entitlement, who has days in advance, who has returned from long leave.
3. Open access for employees to their own balance, the same week. The sooner they look, the cheaper the corrections.
4. Freeze the old file. Stop updating it in parallel — two sources updated simultaneously diverge within three weeks.

How you communicate the balances before the import

The step that prevents every later dispute costs an hour of work and is almost always skipped: each person receives, in writing, their balance at the anchor date and a deadline for flagging a mismatch.

What the message contains

1. The figure — the balance at the anchor date, and separately the days already scheduled for the coming period
2. How it was calculated — one sentence: where the figure comes from and what period it covers
3. The deadline — by when a mismatch can be flagged — two weeks is enough
4. What comes next — that after that date the balance becomes the starting point of the new record

The response rate is low — usually under 10% flag anything. But those ten per cent are exactly the disputes you would have had in December, with the difference that now they are cheap and nobody has a reason to take them personally.

The mistake made at the last moment

Under deadline pressure comes the temptation to import already approved future periods as if they had been taken. They have not: an approved but untaken period is a reservation, not a deduction. Mixed together, they produce balances that match nothing, and correcting them requires reconstructing each case.

Where to start

Choose the anchor date before anything else and write it down. Every decision that follows depends on it, and changing it mid-process means redoing the checks.

Run the nine checks on the file before sending it anywhere. The last one — the sum compared with the old total — takes a minute and catches what the other eight miss.

Send each person their own balance before the import, with a deadline for objections. It is the hour of work that removes every December argument.