PTO Tracking in a Spreadsheet

Most homemade trackers use a tab per employee and a column per month. It looks like a calendar, which is comforting, and it is the layout that fails first. Every question worth asking crosses employees: who is off next week, how many days has the team taken, which weeks have thin cover. A tab per person turns every one of those into a manual sweep.

Use one sheet, one row per absence, and these columns:

• Employee, spelled identically everywhere in the file.
• Leave type, chosen from a short list rather than typed freehand.
• Start and end, as real dates rather than text.
• Half day at the start and half day at the end, as flags.
• Days, calculated rather than typed.
• Deducts from allowance, driven by the leave type.
• Note.

Then a second small sheet with one row per employee holding the start date, the allowance and the carried figure, and a third holding public holiday dates. Three sheets, no merged cells, and no colour carrying meaning that a formula needs to read. That structure is what the CSV on the PTO tracker template page gives you, so you can start from it rather than building it.

Working days between two dates, excluding weekends and a named range of public holidays:

=NETWORKDAYS.INTL(start, end, 1, holidays)

The third argument is the weekend pattern, where 1 means Saturday and Sunday. Change it if your working week is different. The fourth is the range of holiday dates, which you maintain by hand every year and which is the first thing to expire when nobody remembers to update it.

Half days come off afterwards, since NETWORKDAYS has no concept of one:

=NETWORKDAYS.INTL(start, end, 1, holidays) - 0.5*half_start - 0.5*half_end

Days used by one person in a leave year, counting only the types that deduct:

=SUMIFS(days, employee, name, deducts, "Yes", start, ">="&year_start, start, "<="&year_end)

The balance is then carried days plus allowance minus that total. The same formulas work in Google Sheets. In Excel, define the ranges as named ranges rather than as A2:A400, because ranges written by hand are where the drift starts.

Formula drift

Somebody sorts by date. Somebody inserts a row above the range a SUMIFS points at. Somebody pastes values over a calculated cell because the formula was showing an error that day. None of these throw a warning. They produce a number that is merely wrong, in a file that looks exactly as it did yesterday, with no history to walk back to the point where the total was last right.

Proration

A mid year joiner needs a prorated first year allowance. In a spreadsheet that is a hand calculation typed into a cell, once per joiner, using whichever rule the person doing it remembered. Two people in the same company will produce two different answers and neither will be documented. The rules and the worked examples are in the proration guide.

Carryover

This is the one that compounds. A carryover cap has to be applied once at each year boundary, walking forward. A spreadsheet almost always carries the raw unused figure forward and applies the cap once, or applies it to a running total, and both are wrong in ways that surface years later as a balance nobody can explain. The carryover guide works through the difference with numbers.

Absences that cross a boundary

The SUMIFS above buckets an absence by its start date, so a holiday running from 28 December to 4 January lands entirely in the old year. The correct treatment counts each day in the year it falls in, which a formula over a row of ranges cannot do without another sheet holding one row per day.

• Warn you that four people are already off in the week somebody just requested. The data is in the file. The warning is not, because nothing recalculates a per day headcount while you type.
• Compute public holidays for a future year. A holiday range is a list somebody maintains, and it expires silently.
• Show a year at a glance across the whole team without a second layout duplicating the first.
• Stay private once it is shared. A leave sheet emailed around now sits on several machines, sickness records included, with no way to withdraw it.

It genuinely is, sometimes. For under five people on one simple policy with nobody joining mid year, a sheet is fine and the setup cost is nothing. For a closed year that needs archiving, a CSV is a better artefact than any application. And for handing payroll a list of absences inside a date range, a spreadsheet is exactly the right shape, which is why exporting to one matters more than avoiding one.

The honest test is whether anybody has had to reconstruct a balance in the last year. If somebody has, the file has already cost more than it saved.

A dedicated tracker handles the four failures above by construction. Working days and public holidays are computed rather than listed. Proration follows one written rule for everybody. Carryover is recalculated from the entries every time it is displayed, so correcting an old booking corrects every balance after it. And a clash warning appears while the booking is being entered rather than after somebody has already promised the time.

The tracker on this site does all of that in the browser, with no account and nothing uploaded, and still exports the CSV payroll asks for. Use it here, or take the template if a spreadsheet is genuinely what the situation needs.