What this leave tracker is for
Leave is where small HR teams lose the most goodwill. An employee remembers exactly how many casual leaves they took; the company often does not, and the argument surfaces in December when balances matter for encashment or carry-forward. A leave tracker gives you one place where entitlement, accrual, usage and balance are visible for every employee and every leave type.
This template is for companies with roughly 10 to 150 employees that run leave on paper, WhatsApp requests and a manager's memory. It separates the log of individual leave applications from the balance sheet, so a balance is never typed, only calculated. It is set up for 2026 but the year is a single cell.
It handles the four types most Indian private companies use: earned or privilege leave (EL), casual leave (CL), sick leave (SL) and compensatory off. Leave without pay (LWP) is tracked as days so that payroll can apply LOP. Maternity leave and other statutory leaves are recorded in the log with their own type but do not draw down a balance.
What is inside: four linked sheets
The Balances sheet has one row per employee: Emp ID, Name, Date of Joining, then EL Opening, EL Accrued, EL Taken, EL Balance; CL Entitlement, CL Taken, CL Balance; SL Entitlement, SL Taken, SL Balance; Comp-Off Earned, Comp-Off Taken, Comp-Off Balance; and LWP Days. Every Taken column is a SUMIFS over the Leave Log, and every Balance is Opening or Entitlement plus accrual minus Taken. You type only the opening balances, entitlements and EL accrual.
The Leave Log is the transaction sheet: one row per application with Emp ID, Name, Date From, Date To, Days, Type (EL, CL, SL, CO, LWP, ML or other), Reason, Approved By and Status (Approved, Pending, Rejected, Cancelled). Only rows with Status equal to Approved count toward balances. Days can be typed as 0.5 for a half day.
The Holidays 2026 sheet is a list of date and holiday name with a note to enter your state's notified list; national holidays vary in number by state and establishment type, so the sheet is intentionally not pre-filled. The Calendar sheet is a twelve-month grid for 2026 that shades weekends and any date found in the Holidays list, useful for pinning up or checking sandwich situations.
- EL Taken: =SUMIFS(Log!F:F, Log!A:A, A5, Log!G:G, "EL", Log!J:J, "Approved").
- EL Balance: =EL Opening + EL Accrued − EL Taken; CL and SL balances follow the same pattern without accrual.
- Comp-Off Earned is typed from your comp-off approvals; Comp-Off Balance = Earned − Taken.
- Days in the Log can be computed as =NETWORKDAYS.INTL(From, To, weekend code, Holidays!A:A) if your policy excludes weekly offs and holidays.
How to use it through the year
In January, enter every employee's opening EL balance carried forward from 2025 and the year's CL and SL entitlements from your policy. Enter the holiday list. Then, whenever a leave is approved, add one row to the Leave Log with Status Approved. Do not touch the Balances sheet except to update EL Accrued.
EL accrual depends on your policy. If EL accrues monthly, say 1.5 days per month for an 18-day annual entitlement, add 1.5 to EL Accrued at each month end, or replace the cell with a formula that multiplies the monthly rate by months completed since the later of 1 January and the Date of Joining. If EL is credited in full on 1 January, type the annual figure once.
At month end, filter the Leave Log for the month and copy LWP Days and any unpaid absences to the monthly attendance sheet so payroll applies LOP correctly. At year end, use the EL Balance column to compute carry-forward within your cap and encashment for anything above it; the leave encashment calculator does the rupee arithmetic.
- Freeze the header row on the Leave Log and sort by Date From so overlapping applications stand out.
- Add data validation to Type and Status so a typo such as 'El' does not silently drop out of the SUMIFS.
- Share a read-only PDF of each employee's row, never the whole Balances sheet.
How to customise it
Add leave types by inserting a Taken and Balance pair in Balances and a matching value in the Type validation list; typical additions are paid paternity leave, bereavement leave and marriage leave. If your company uses a single 'paid leave' bucket instead of EL, CL and SL, keep just the EL columns and rename them.
For pro-rata entitlement of mid-year joiners, replace the CL and SL Entitlement cells with =ROUND(Annual × (12 − MONTH(DOJ) + 1) ÷ 12, 0) for employees whose Date of Joining falls in the current year. For comp-off expiry, add an Earned On date column in a separate comp-off log and count only entries within your validity window, commonly 30 to 90 days; the comp-off policy guide discusses reasonable windows.
If you want the tracker to flag sandwich leave, add a helper column in the Log that checks whether the day before Date From and the day after Date To are weekly offs or holidays; the sandwich leave rule article explains what to count.
Leave rules the tracker should reflect
Statutory leave in India is set by the law that covers your establishment. For factories, the Factories Act grants one day of earned leave for every 20 days worked to adults who have worked 240 days in the calendar year, with carry-forward up to 30 days. For shops, offices and commercial establishments, your state's Shops and Establishments Act sets earned, casual and sick leave, and the figures vary by state, commonly in the range of 12 to 18 days of EL with separate CL and SL. Do not assume a single national CL or SL number.
Company policy can be more generous than the statute, never less. Write down the accrual method, carry-forward cap, encashment rule and the approval chain in a leave policy; the leave policy for private companies guide has a structure you can adopt, and the earned leave rules and comp-off glossary entries define the terms your tracker uses.
Maternity leave of 26 weeks for the first two children under the Maternity Benefit Act (establishments with 10 or more employees) is paid leave outside the EL, CL and SL balances; log it with its own type so attendance and payroll treat it correctly.
When the tracker stops working
The Excel tracker works while one person owns it. It fails when managers approve leave on WhatsApp and forget to tell HR, when an employee disputes a balance and there is no application trail, when the same person is on approved leave and still rostered for a shift, and when LWP has to be re-typed into payroll every month.
Attend Mitra moves leave into the employee app: the employee applies, the manager approves, the balance updates, and approved leave shows on the attendance record and the roster, with a warning if someone on leave is scheduled. Leave policies with accrual, paid, casual and sick types, and LOP flowing straight into the payroll run are part of the leave management solution; the leave policies setup guide shows how the rules in your tracker map to the software.