Ultimate Vacation / PTO Tracker Spreadsheet Template for Google Sheets & Excel

The roster, and the terms each person is on. Each row carries a name, a department, a start date, the annual allowance, the days carried over from last year and a daily rate. Total available works itself out from the allowance plus the carry-over. Carry-over is the reason this sheet exists rather than a single allowance column. A balance that ignores what someone brought forward is wrong from January, and it stays wrong all year. The daily rate is what turns unused days into a figure the finance side recognises - the same balance, expressed as what it would cost to pay out. Worth knowing about this build: the roster feeds the balances, the calendar and the accrual sheets, and all twenty rows carry through to every sheet that reads from it - not just the first ten.

A row per request. Each carries who asked, what kind of leave it is, the dates it runs between, how many days that comes to, whether it is approved, who approved it and a note. The status column is doing real work. Balances count approved requests only, so a request sitting unapproved is visible in the log without moving anyone's remaining days - which is the behaviour you want when a request has been made but not yet answered, and the reason a log beats a balance you edit by hand. Employee, type and status are dropdowns rather than free text. That is not decoration: a balance that matches on a name cannot match "D. Chen" against "David Chen", and the dropdown is what keeps the log and the roster speaking the same language.

One row per person, with the used figure pulled from the approved entries in the log rather than typed. Remaining follows from the total available, utilization expresses it as a share, and a status flag reads the balance: below three days left is marked low, three to five is a watch, and anything above reads healthy. Those thresholds are fixed in the sheet rather than settable, which is worth knowing if your policy sits elsewhere - a team with a 25-day allowance and one with 15 get the same flag at the same absolute number. The utilization column is the one that travels across both.

Days taken per person across the twelve months, with totals down the side and along the bottom. Every cell reads from the PTO Log automatically, matched by employee and by month, so an approved request moves the right cell without anything retyped. What it answers is the coverage question rather than the balance one: not how much time someone has left, but which months the team is thin. That tends to show up as a column rather than a row - a July where six people booked at once is a staffing problem no individual balance reveals.

Each person's annual allowance divided across the twelve months, so a 20-day allowance shows as the same figure accruing every month. It is the simple accrual model - equal monthly amounts rather than anything keyed to hours worked or length of service. It answers the mid-year question: how much someone has actually earned by June, as opposed to how much they will have by December. Those are different numbers, and the difference is what makes a January holiday feel generous and a December one feel overdue.

Six figures and two charts. The top row carries total available, total used across approved requests and total remaining. The row beneath carries average utilization, a count of anyone under three days, and the PTO liability - the remaining days valued at each person's daily rate. That last figure is the one an accountant asks for and a spreadsheet rarely offers. Unused leave is a real obligation, and expressing it in money rather than days is what puts it into the same conversation as everything else on the balance sheet. Beneath, an employee summary table repeats each person's available, used, remaining, utilization and status in one place.