Tracking Leave in Excel: Template and Limits
A per-employee Excel sheet with opening balance, accrual, taken and balance works for tracking leave in Sri Lanka up to about 20 staff. Then it breaks.
On this page
- The columns the sheet must have
- Building first-year proration into the sheet
- Handling Poya days, holidays and half-days
- The five ways an Excel leave tracker goes wrong
- Approval trails a spreadsheet cannot give you
- Signs it is time to move
- Migrating balances without losing history
- Frequently asked questions
- Is Excel legal for tracking leave in Sri Lanka?
- What is the biggest single mistake in Excel leave sheets?
- Should Poya days be deducted from the leave balance?
- Can casual leave be carried into next year's sheet?
- How many staff can one spreadsheet realistically handle?
A per-employee sheet with opening balance, accrual, days taken and running balance works fine for tracking leave. As a rule of thumb it holds up to about 20 staff. Past that, approvals, first-year proration and audit trails outgrow what a spreadsheet can hold safely.
The columns the sheet must have
Build one row per employee, one sheet per leave year. Use these columns:
| Column | What goes in it |
|---|---|
| Employee name and join date | Needed to work out first-year proration |
| Leave type | Annual or casual (see annual leave vs casual leave) |
| Opening balance | Carried from last year, per your policy |
| Earned this year | 14 annual, 7 casual, or the first-year figure |
| Taken | Each date, with a reason |
| Balance | Opening plus earned, minus taken |
Keep annual and casual leave in separate blocks. They accrue differently.
Building first-year proration into the sheet
A new joiner does not get the full 14 annual days or 7 casual days straight away. Annual leave for the first year depends on the quarter they joined:
| Joined between | Annual leave, first year, taken the following year |
|---|---|
| 1 January – 31 March | 14 days |
| 1 April – 30 June | 10 days |
| 1 July – 30 September | 7 days |
| 1 October – 31 December | 4 days |
Casual leave builds up at 1 day for every 2 completed months of service, usable that same year. Put both rules in a lookup formula keyed to the join date, so a new row calculates itself. The leave entitlement calculator checks one employee’s figure if you want to spot-check the formula.
Handling Poya days, holidays and half-days
Poya days and Gazetted mercantile holidays are paid holidays, not leave. Do not deduct them from the annual or casual balance. If your policy allows half-day leave, use 0.5 in the “taken” column. The Act is silent on half-days, so this is your own policy choice, applied consistently across the sheet. See how to write a leave policy for setting these choices out in writing.
The five ways an Excel leave tracker goes wrong
- One shared file with no version control lets two managers edit at once, so one approval disappears.
- No first-year proration, so new joiners get the full 14 days from day one, which overpays leave.
- Poya days and holidays mixed into the leave balance, understating what staff are actually owed.
- Balances not updated after approval, so payroll finds out about no-pay days too late.
- Nothing shows who approved a request, or when. That is the gap a Labour Officer cannot see in a spreadsheet.
Approval trails a spreadsheet cannot give you
Section 18 of the Shop and Office Employees Act requires a record of leave taken and overtime worked, open to inspection. A spreadsheet can hold the numbers. It cannot show who approved a request, when, or whether the manager saw the balance before saying yes. Email chains and chat messages fill the gap until someone leaves and the thread is lost.
Signs it is time to move
Move off Excel when any of these show up:
- More than one person edits leave balances, and mistakes start reaching payroll.
- Staff count passes about 20, so a single sheet takes real time to update each month.
- You cannot answer “how many people are on leave next Tuesday” without opening the file.
- A Labour Officer visit or audit needs the leave register produced quickly, with approvals attached.
The same headcount point applies to payroll generally: see when to switch from Excel to payroll software.
Migrating balances without losing history
Export the current opening balance, this year’s earned days and days taken for every employee before switching systems. Carry over the raw numbers, not a recalculated guess. Keep the old spreadsheet as a backup, in case a past balance is ever questioned.
Do this automatically in Humanised. A leave management system lets you set an accrual rate from the joining date, and keeps every approval on record. There is a free 7-day assisted trial, set up by the Humanised team on your own data.
This is general payroll guidance. Every case is different. For your own case, speak to a payroll practitioner or a lawyer.
Frequently asked questions
Is Excel legal for tracking leave in Sri Lanka?
Yes. Section 18 of the Act requires a record of leave and overtime, not a specific format. A well-kept spreadsheet meets that section. The risk is accuracy and approval trails, not the file type.
What is the biggest single mistake in Excel leave sheets?
Skipping first-year proration. Giving a new joiner the full 14 annual days and 7 casual days from day one overpays leave against the leave entitlements in Sri Lanka table.
Should Poya days be deducted from the leave balance?
No. Poya days and Gazetted mercantile holidays are paid holidays under the Act, not leave. Deducting them understates what the employee is actually owed.
Can casual leave be carried into next year’s sheet?
No. Casual leave is not encashable and lapses at year end unless your policy is more generous. Carry over only the annual leave balance, per your carry-forward policy.
How many staff can one spreadsheet realistically handle?
About 20 is the practical limit, once approvals and audit trails start slowing the sheet down. It is a working guideline, not a legal figure.
Was this article helpful?
Your feedback helps us improve these guides.