Log leave once as a date range and the rest calculates itself — entitlement, carry-over, days taken, requests still pending and days remaining, per employee. Weekends and public holidays are excluded automatically. Excel and Google Sheets, no email required.
Free download. No email required.
| Employee | Allowance | Taken | Booked | Remaining | % Used |
|---|---|---|---|---|---|
| Aisha Khan | 28 | 12 | 5 | 11 | 61% |
| Ben Cooper | 20 | 6 | 0 | 14 | 30% |
| Carla Mendez | 24 | 18 | 4 | 2 | 92% |
| David Osei | 25 | 9 | 0 | 16 | 36% |
| Elena Rossi | 25 | 4 | 9 | 12 | 52% |
Preview of the template — the download includes formulas and all tabs.
The tab you will actually live in. Allowance, days taken, days still booked in the future and days remaining — all pulled from the leave log with SUMIFS, so nothing is typed twice. Approved and pending are kept apart on purpose: a balance that ignores booked leave is how people end up over-drawn in December.
Best for: answering “how many days do I have left?” without opening anything else.
| Employee | Allowance | Taken | Booked | Remaining | % Used |
|---|---|---|---|---|---|
| Aisha Khan | 28 | 12 | 5 | 11 | 61% |
| Ben Cooper | 20 | 6 | 0 | 14 | 30% |
| Carla Mendez | 24 | 18 | 4 | 2 | 92% |
| David Osei | 25 | 9 | 0 | 16 | 36% |
| Elena Rossi | 25 | 4 | 9 | 12 | 52% |
One row per request: who, what kind of leave, first day off, last day off. NETWORKDAYS counts the working days between them and subtracts anything on the Holidays tab, so a Friday-to-Monday break costs two days rather than four.
Best for: recording requests as they are approved, and keeping an auditable history.
| Employee | Type | First Day | Last Day | Working Days | Status |
|---|---|---|---|---|---|
| Aisha Khan | Vacation | 16 Mar | 20 Mar | 5 | Approved |
| Ben Cooper | Sick | 04 Feb | 05 Feb | 2 | Approved |
| Carla Mendez | Vacation | 06 Jul | 17 Jul | 10 | Approved |
| David Osei | Personal | 11 May | 11 May | 1 | Approved |
| Elena Rossi | Vacation | 21 Dec | 31 Dec | 7 | Pending |
The same data spread across twelve months so you can see coverage rather than balances. Useful before you approve anything in July — three people already off in the same month is the thing a balance sheet will never tell you.
Best for: spotting clashes and planning cover a quarter ahead.
| Employee | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | … | Total |
|---|---|---|---|---|---|---|---|---|---|---|
| Aisha Khan | – | – | 5 | – | 2 | – | 5 | – | … | 12 |
| Ben Cooper | – | – | – | 3 | – | – | 3 | – | … | 6 |
| Carla Mendez | 2 | – | – | – | 4 | 2 | 10 | – | … | 18 |
| David Osei | – | 4 | – | – | 1 | – | 4 | – | … | 9 |
| Elena Rossi | – | – | – | – | – | 4 | – | – | … | 4 |
Set up once a year: annual entitlement, days carried over from last year, and the total allowance those two produce. Everything else in the workbook reads from this tab, so a mid-year change to someone's entitlement flows through every balance.
Best for: the January setup, and pro-rata changes for new starters.
| Employee | Department | Annual Entitlement | Carried Over | Total Allowance |
|---|---|---|---|---|
| Aisha Khan | Operations | 25 | 3 | 28 |
| Ben Cooper | Sales | 20 | 0 | 20 |
| Carla Mendez | Support | 22 | 2 | 24 |
| David Osei | Operations | 20 | 5 | 25 |
| Elena Rossi | Finance | 25 | 0 | 25 |
On the Employees tab, add each person with their annual entitlement in days and anything carried over from last year. Total allowance calculates itself. Add your public holidays to the Holidays tab so they never count against anyone's leave.
Add a row to the Leave Log: employee, leave type, first day off, last day off. Working days are counted for you. Set Status to "Approved" or "Pending" — the balances count them separately so future bookings are already reserved.
The Balances tab updates the moment you save. Sick leave is tracked in its own column and does not reduce the vacation allowance. Use the Year Calendar tab before approving requests to check nobody has booked the same fortnight.
Most vacation trackers you will find are a wall calendar in spreadsheet form: a grid of days you shade in. They look right and they fail quietly, because a shaded cell is not a number. Somebody still has to count coloured squares to answer the only question anyone ever asks, which is how many days are left.
This template inverts that. The log is the record, the balance is derived, and the calendar is a view built from both. Three things follow from that choice:
Every one of these is already in the template. They are here so you can adapt it, or rebuild the same logic in a sheet you already have. All of them work unchanged in Google Sheets. Row 6 is the first data row throughout.
| Calculate | Formula | What it does |
|---|---|---|
| Working days in a request | =NETWORKDAYS(C6,D6,Holidays!$A$6:$A$45) | Counts weekdays inclusive of both dates and drops listed public holidays. |
| Days taken (approved) | =SUMIFS(Log!$E:$E,Log!$A:$A,$A6,Log!$B:$B,"Vacation",Log!$F:$F,"Approved") | Totals one person's approved vacation. Swap the status for pending requests. |
| Total allowance | =N(D6)+N(E6) | Annual entitlement plus carry-over. N() keeps it at zero rather than erroring on a blank row. |
| Remaining balance | =N(B6)-N(C6)-N(D6) | Allowance minus taken minus booked. Pending requests are reserved, not ignored. |
| Days taken in a given month | =SUMIFS(Log!$E:$E,Log!$A:$A,$A6,Log!$C:$C,">="&B$5,Log!$C:$C,"<="&EOMONTH(B$5,0)) | Bounds the log by month start and end — the Year Calendar tab uses this twelve times. |
| Pro-rata entitlement for a new starter | =ROUND($D$2/12*(13-MONTH(C6)),1) | Full-year entitlement scaled by whole months remaining. Round to halves or your policy's unit. |
Sheet names are shortened to Log! for readability — the workbook uses 'Leave Log'!.
These three get used interchangeably and they answer different questions. Picking the wrong one is why so many teams end up maintaining a spreadsheet that never quite tells them what they need.
| Template | Question it answers | Shape |
|---|---|---|
| Vacation tracker | How many days does each person have left? | Log of date ranges → derived balance |
| PTO tracker | Who is off on any given day? | Day-by-day grid marked with leave codes |
| Absence tracker | Is unplanned absence becoming a problem? | Occasions, absence rate, Bradford Factor |
If you want the day-by-day view, the PTO tracking template is the one to take. If what you are really chasing is short, frequent absences rather than planned leave, use the employee absence tracker — it calculates absence rate and Bradford Factor, which a vacation balance cannot.
A single file works well for a handful of people and one person maintaining it. It usually breaks for one of three reasons, in this order:
ClockIt does the same job without the shared file: each employee sees their own balance, requests go to their manager for approval, and balances update on approval — including monthly accruals, carry-over caps and pro-rata entitlements. It also links leave to attendance and time tracking, so a booked day off and an unexplained absence never look the same in your records.
The reliable pattern is a log plus a balance, not a calendar you colour in by hand. Record each request as one row with a start and end date, count the working days between them with NETWORKDAYS, then total each person's days with SUMIFS on a separate balances tab. This template is already built that way — you only ever type the request.
Remaining = (annual entitlement + carry-over) − approved days taken − days already booked. In the template that is =N(B6)-N(C6)-N(D6), where the taken and booked figures come from =SUMIFS('Leave Log'!$E:$E,'Leave Log'!$A:$A,$A6,'Leave Log'!$B:$B,"Vacation",'Leave Log'!$F:$F,"Approved"). Subtracting pending requests as well as approved ones is what stops a balance looking healthier than it is.
Use NETWORKDAYS rather than subtracting one date from another: =NETWORKDAYS(C6,D6,Holidays!$A$6:$A$45). It counts Monday to Friday inclusive and drops any date listed on the Holidays tab. A Thursday-to-Monday trip then costs two days, which is what the employee expects to see.
Almost never — they are different entitlements and in many places sick leave is separately protected by law. This template logs sick leave the same way but reports it in its own column on the Balances tab, so it is visible without reducing anyone's vacation days. The Leave Types tab controls which types deduct.
Enter it once a year, by hand, in the Carried Over column — after your cut-off date and after applying whatever cap your policy sets. Resist the urge to calculate it in the sheet: carry-over is a policy decision with exceptions, and a formula will confidently produce the wrong answer for the one employee whose case is unusual.
A PTO tracker is usually a day-by-day grid you mark up as leave is taken. This one is balance-driven: you log a date range and the days, totals and remaining balance are derived. If you want the grid version, use the PTO tracking template instead — many teams keep both, one for the calendar view and one for the balances.
Thirty employee rows and 120 leave requests as shipped, which comfortably covers a year for a small team. Add rows by copying an existing one — the formulas use absolute ranges, so they extend cleanly. Past roughly 30 people a shared spreadsheet starts costing more time than it saves.
Not in a spreadsheet — that is its real limit. Anyone who can see their balance can also change it, and you cannot give one person edit access to their own row only. ClockIt gives each employee their own balance and a request button, routes it to their manager, and updates the balance on approval. The free plan covers small teams.
Daily, weekly, monthly and yearly attendance grids with auto totals.
Occasions, days lost, absence rate and Bradford Factor per employee.
Track vacation, sick and personal leave balances per employee.
Weekly, biweekly, monthly and project timesheets for payroll.
Weekly, bi-weekly, monthly and shift schedules for your team.
Printable sign-in log for recording start and finish times.
A simple leave request form your team can fill in and submit.
Work out statutory leave entitlement, pro-rata and part-year.