Time off tracking template

This is the per employee sheet, and it does the arithmetic a PTO spreadsheet is supposed to do and usually gets wrong in one specific place. Give it the hours your policy grants for a full year, the number of pay periods you run, how many of those have gone, what carried in from last year, what has been taken and what has been approved for a date still to come. It returns the accrual per pay period, the hours accrued so far including carry-in, the balance available to book right now in hours and in days, what is still to accrue before year end, and where the balance lands at the year boundary if nothing else is booked. The place spreadsheets go wrong is approved but not yet taken time: on the worked example that one column is the difference between telling someone they have 43 hours and telling them the truth, which is 27.

The accrual, off your policy and your pay periods

120 hours a year over 24 semimonthly periods accrues 5 hours a period, and fifteen periods in that is 75 hours earned. Enter your own annual hours and your own period count and the rate follows; a mid year starter is simply fewer completed periods, not a special case that needs its own spreadsheet tab.

Carry-in, held apart from this year's hours

16 hours carried in from last year takes the accrued figure to 91. It is kept as its own input rather than folded into the annual grant because it behaves differently at year end: your carryover cap applies to it, and in most policies it is the first thing spent.

Taken and approved, both subtracted, which is where sheets go wrong

48 hours taken and 16 already approved for a future date leave 27 hours available to book, or three working days. Almost every spreadsheet subtracts the first and forgets the second, tells the employee they have 43 hours, and books two people off the same week. The paid plan holds approved time against the person automatically.