Keeping time off balances right without a spreadsheet
The five places a leave spreadsheet goes wrong, a worked balance for a part-time employee, and what to set up instead.
7 min readFor owners, managers and payroll
In short
- A balance is four numbers: what was carried in, what has been earned, what has been taken and what is booked.
- Spreadsheets go wrong at the edges: accrual, part-time patterns, public holidays, carry-over and requests still waiting.
- Count time off in working days from each person's own pattern, not calendar days.
- Show the balance at the moment of asking. It prevents most of the questions.
- Move over at a clean point with opening balances, and keep the old sheet as a record.
On this page6 sections
Nearly every team starts by tracking time off in a spreadsheet, and for four or five people it works well. One row per person, an allowance, a column for days taken, a formula for what is left. The trouble starts quietly as the team grows. Someone goes part-time. Someone joins in March. Two people ask about the same week in August, and the person who maintains the sheet is on holiday herself.
The spreadsheet does not usually fail with a wrong formula. It fails because a balance depends on rules and events that someone has to apply by hand, every month, without forgetting. This guide walks through where that goes wrong and what to put in its place.
What a balance actually is
It helps to be exact about the number everyone is asking for. A time off balance at any date is made of four parts.
| Part | What it is | Where it goes wrong by hand |
|---|---|---|
| Carried in | Unused days brought from last year | Limits and expiry dates are applied late or not at all |
| Earned | Allowance given or built up so far this year | Monthly amounts are missed, or not adjusted for part-time and joiners |
| Taken | Approved days that have already happened | Counted in calendar days, or including public holidays |
| Booked | Approved days still to come | Left out, so the balance looks bigger than it is |
- Part
- Carried in
- What it is
- Unused days brought from last year
- Where it goes wrong by hand
- Limits and expiry dates are applied late or not at all
- Part
- Earned
- What it is
- Allowance given or built up so far this year
- Where it goes wrong by hand
- Monthly amounts are missed, or not adjusted for part-time and joiners
- Part
- Taken
- What it is
- Approved days that have already happened
- Where it goes wrong by hand
- Counted in calendar days, or including public holidays
- Part
- Booked
- What it is
- Approved days still to come
- Where it goes wrong by hand
- Left out, so the balance looks bigger than it is
Available balance is carried in plus earned, minus taken, minus booked. If any one of the four is maintained by memory, the answer drifts.
Five places a spreadsheet goes wrong
1. Accrual is done by hand
If time off builds up through the year instead of being given on day one, someone has to add each person's monthly amount, every month. Miss a month and every balance is short. Add it twice and every balance is generous. An accrual rule that runs on its own does not forget, and does not need the one person who understands the sheet to be at work.
2. Part-time and changing patterns
A full allowance has to be scaled for people who work part of a week, and a week off is a different number of days for different people. For someone who works Monday to Wednesday, a week away uses three days of leave, not five. A spreadsheet that subtracts calendar days, or the same five days for everyone, is wrong for every part-time employee, in a direction that depends on the formula.
3. Public holidays and non-working days
A request from Thursday to the following Tuesday spans six calendar days. It might be four working days, or three if a public holiday falls on the Monday. Counting that by eye is where most small errors come from. The count should come from the person's working days and the holiday calendar they follow.
4. Carry-over at year end
At the end of the leave year, unused days either carry over, carry over up to a limit, or are lost. Many policies add an expiry: carried days must be used within a few months. Applying that to every row, on the right date, is a job that gets done late in most companies, and for a while nobody is sure what their real balance is.
5. Requests that are still waiting
A spreadsheet normally records approved leave only. So when two people ask for the same week on the same day, each request looks fine on its own. And an employee can ask for more days than they have, because the days already requested are not yet in the sheet.
A worked balance
Here is one balance worked through by hand, to show how many rules sit behind a single number. The amounts are examples, not recommendations.
- Policy: 24 days a year for full-time staff, building up monthly, with a leave year from January to December.
- Emma works 24 hours a week against a full-time 40, so she gets 60 percent: 24 × 0.6 = 14.4 days a year, or 1.2 days a month.
- Carried in from last year: 2 days.
- Earned by 30 June: 6 months × 1.2 = 7.2 days.
- Taken: a week off in April. She works Monday to Wednesday, so it used 3 days.
- Booked: 2 days in August, already approved.
- Available: 2 + 7.2 − 3 − 2 = 4.2 days.
Seven lines, five rules, for one person on one date. None of the sums is hard. The difficulty is doing them for everyone, every month, the same way, and that is exactly the kind of work that should not depend on a person remembering.
What to set up instead
Whatever tool you use, the setup is the same four pieces. Once they exist, balances are worked out for you instead of maintained by you.
- A policy for each kind of time off: the yearly allowance, whether it is given up front or builds up monthly, how much carries over and when carried days expire.
- Each person's contract hours and working days, so allowances are scaled for part-time staff and requests count the right days.
- A public holiday calendar for each person, so holidays are not counted as leave.
- An approver for each team or location, and a shared view of who is off, so clashes are seen before a request is approved.
In ClockinBee these are Time off policies and Public holidays under Settings, and the working details on each person's page in Team. Balances build up every night under each person's policy, and carry-over and expiry are applied on the policy's dates. When someone requests time off they see their balance on the same screen, and weekends they do not work and public holidays are left out of the count.
Moving over from a spreadsheet
- Pick a clean date, ideally the start of a month or of the leave year.
- Bring the spreadsheet up to date as of that date, and ask each person to confirm their figure. It is far easier to settle a disagreement now than in December.
- Enter each person's balance on that date as their opening balance. If you have many people, import them from a file.
- Add leave that is already approved for future dates, so booked days are counted.
- From that date, take requests in one place only. Keep the old spreadsheet, read-only, as the record of what came before.
Common questions
Should we count time off in days or hours?
Days are simpler when people work the same length of day. Hours are fairer when shift lengths vary a lot, because a day off from a ten-hour shift is worth more than a day off from a four-hour one. Choose one unit per kind of time off and keep to it.
Can someone take leave they have not earned yet?
That is a policy decision, not a calculation. With an up-front allowance the question does not come up. With monthly accrual, some employers let people go below zero and some do not. Decide it, write it down, and check what applies if someone leaves owing days.
What happens to the balance when someone leaves?
In many places unused earned leave is paid out, and leave taken beyond what was earned may be recovered. The rules differ by country and contract, so check the ones that apply to you. Either way you need an exact balance on the leaving date.
How many kinds of time off do we need?
As few as you can. Annual leave and sickness cover most teams, with unpaid leave as a third. Each extra kind is another balance for people to understand.
How to do this in ClockinBee
- Set up time off policiesAllowances, how time off builds up, part-time, carry-over, notice and blocked dates.
- Request time offPick your dates, see how many days it uses and what you will have left, then send.
- Public holiday calendarsGive each location or person the right public holidays, and add your own days.
- Approve timesheets and time offApprove everything that is ready in one go, and look closely at what needs review.
- Import people, locations and past hours from a fileBring in a CSV from payroll or HR. Every line is checked before anything is saved.
Try it with your own team
1 month free with every feature, no card needed. $6 per user a month after that.
This guide is general information, not legal, tax or payroll advice. Working time, pay and time off rules differ by country and contract, so check the rules that apply to you.