How to track employee leave in Excel
- Set your leave types and allowances. Enter each leave type and the days allowed per year, and pick your weekly off days.
- Download the Excel tracker. The workbook has a Leave Tracker summary, a Leave Log and a Holidays sheet with formulas ready.
- Add employees and holidays. Type employee names on the Leave Tracker sheet and your public holidays on the Holidays sheet.
- Record each leave. On the Leave Log add the employee, leave type, from and to dates, and Y for a half day. Working days are counted automatically.
- Check balances. The Leave Tracker shows allowed, taken and remaining leave per employee and type, updated as you go.
Leave balance formula
Every leave balance comes down to one line:
In the template, “taken” is added up from the Leave Log for each employee and leave type:
Counting leave days without weekends and holidays
A leave from Friday to Monday is four calendar days but only two working days if Saturday and Sunday are off. The template counts days with NETWORKDAYS.INTL, which skips your weekly offs and any date on the Holidays sheet:
The seven-character code is Monday to Sunday, with 1 for a day off: 0000011 is Saturday and Sunday off, 0000001 is Sunday only, and 0000100 is Friday only. Half-day leaves count as 0.5. To check a single leave, use the working days calculator.
Common leave types
- Annual leave (earned or privilege leave): planned time off, often 12 to 30 days a year, sometimes with carry-forward.
- Sick leave: for illness, sometimes needing a medical certificate after 2 or 3 days.
- Casual leave: short-notice personal days, usually 6 to 12 a year, often not carried forward.
- Unpaid leave (loss of pay): time off beyond paid allowances, deducted from salary.
- Maternity, paternity and bereavement leave: set by law or company policy.
- Compensatory off: a day off earned by working on a holiday or weekly off.
Carry forward and the leave year
Most organizations reset leave at the start of a leave year, either January or the start of the financial year (for example April). Unused days are either lost or carried forward up to a limit, such as 5 or 10 days. At year end, add each person’s carried-forward days to next year’s allowance in a new copy of the tracker.
Keeping leave in a spreadsheet works until requests arrive by message, someone forgets to log one, or two people edit the file at once. Clocked Live handles leave requests and approvals from the phone, counts only working days (including each person’s own weekly off and holidays), supports half days, leave years and carry-forward, and shows every balance live. See also the free monthly attendance sheet template.