What is on the employee vacation calendar
- Day codes
- One letter per day taken: V vacation, S sick, P personal, F floating holiday, H company or federal holiday, half days marked ½. Using the same codes on every sheet makes the year-end count quick.
- Federal holidays pre-marked
- On the 2027 annual calendar the observed federal holidays are already printed as H, and days that do not exist (February 29 to 31, April 31) are dashed out.
- Allowance, carry-over, balance
- The annual PTO entitlement in days, any days carried from 2026 under your policy, the days used (the sum of the month column) and the balance. Excel calculates the last two.
- Staff out row
- On the team planner, the count of people away each day. Compare it with the minimum staffing at the top before approving a request.
- Accrual ledger
- For PTO that accrues per pay period: each accrual, each absence in hours, and a running balance. Excel keeps the balance and the closing total.
- Request and approval
- The dates, hours, type and cover for a request, the balance before it, and the manager's decision and signature.
Why track PTO on a calendar
Most PTO problems are visibility problems. A manager approves a week off without remembering that someone else on the team is away the same week; an employee reaches December with twelve unused days and a use-it-or-lose-it policy; payroll pays out the wrong balance when someone leaves because the last six months of absences were in emails. A vacation calendar fixes all three: approved leave goes onto a shared page as soon as it's approved, the balance is updated at the same time, and the record exists when anyone needs it. HR software does this too, but plenty of small businesses with five to fifty employees run it perfectly well on paper or a spreadsheet.
The three sheets
- 2027 annual PTO calendar: one per employee, kept by the manager or HR. Twelve months on one landscape page, holidays already in, and the balance at the bottom. At year end it shows exactly how many days were taken and when.
- Team monthly planner: one per team per month, on the wall or a shared drive. Before approving a request, look down the column for those dates and at the "staff out" row.
- PTO request and balance ledger: for companies where PTO accrues in hours each pay period. The request goes at the top; the ledger keeps the running balance so the "balance before request" line is always right.
PTO accrual explained
Many employers grant PTO gradually rather than all on January 1. The arithmetic is simple: annual hours divided by pay periods. Fifteen days a year is 120 hours; over 26 biweekly pay periods that's 4.62 hours per period. Twenty days (160 hours) is 6.15 hours per period which is the rate in the sample. Accrual policies often cap the balance (for example at 1.5 times the annual amount) and set rules for carry-over. A handful of states, California among them, treat accrued vacation as earned wages: it can't be forfeited and must be paid out when an employee leaves. Many states and cities also require paid sick leave, accrued at a set rate such as one hour for every 30 hours worked. Check the rules where your employees work and write your policy down; the sick leave tracker covers sick leave separately if your state requires it.
A simple approval routine
- The employee fills in the request (or the vacation request form) with dates, hours and who will cover.
- The manager checks the team planner for clashes and blackout dates, and the ledger for the balance.
- If approved, the manager signs, the dates go on the team planner and the employee's annual calendar, and the hours go in the ledger.
- Payroll is told if the absence affects pay (unpaid leave, or a payout of accrued PTO). The payroll calendar shows the cutoff for each pay date.
Answer requests within a set time, a week is common, and approve in the order they arrive when two people want the same dates. Fairness here does more for morale than almost any other policy.
Editing the files
The Excel annual calendar totals the days column and calculates the balance; the ledger keeps a running balance and closing total. Change the codes or add your company holidays (the day after Thanksgiving, Christmas Eve) in the cells. The team planner has room for fifteen people; copy the sheet for each month or each team. In Word every sheet is a table you can extend. The ODT and ODS versions open in LibreOffice and Google Sheets. None of these files store anything online, which keeps employee leave records inside your company.
Questions
How do I track employee vacation days?
Give each employee an annual PTO calendar with their allowance and carry-over at the bottom, mark each day off with a code when it's approved, and let Excel calculate the balance. Use the team planner to check for clashes before approving.
How is PTO accrual calculated?
Annual PTO hours divided by the number of pay periods. For example, 20 days (160 hours) over 26 biweekly periods is about 6.15 hours per pay period; over 24 semi-monthly periods it's 6.67 hours.
Are the 2027 holidays included?
On the annual calendar, yes: the observed federal holidays are pre-marked H. Add any extra company holidays yourself.
Does the PDF calculate the balance?
No. The fillable PDF is for typing. The Excel and ODS versions calculate days used, the balance, the running ledger balance and the closing balance.
Do I have to pay out unused vacation?
It depends on the state and your written policy. Some states, including California, require accrued vacation to be paid when employment ends and don't allow use-it-or-lose-it policies. Check your state labor department; this page is not legal advice.
