What it's for
- US employers who track PTO in a spreadsheet.
- Accrual by pay period: weekly, biweekly, semimonthly or monthly.
- Partial days, like 3 hours for an appointment.
What's inside
- How to use
- Instructions, the color key and the calendar codes.
- Settings
- Plan year start, pay frequency and the date to show balances as of.
- Team
- Each person's hours per workday, PTO and sick accrual per pay period, carryover and optional caps.
- Time off log
- One row per booking: who, PTO, sick, unpaid or other, first and last day, or the exact hours.
- Calendar
- Pick a month to see who is out each day, with company holidays shaded.
- Balances
- Accrued so far, used or booked, current balance and the projected balance at plan-year end.
- Holidays
- Your company holidays. Pre-filled with the 2027 federal holidays, which you can change.
Preview
| Time off | Dates | Hours counted |
|---|---|---|
| Sick | Wed, Feb 10 (3 hours) | 3 |
| PTO | Mon, May 24 to Fri, May 28 | 40 |
| PTO | Fri, Jul 2 to Tue, Jul 6 (Jul 5 is a holiday) | 16 |
Casey works 8-hour days and accrues 4 hours of PTO and 2 of sick time per pay period, with 16 hours of PTO carried over and a 120-hour PTO cap. By June 30, 12 pay periods are complete: PTO accrued is 16 + 12 × 4 = 64 hours, minus 56 used or booked leaves 8. Sick time is 12 × 2 = 24 hours, minus 3 leaves 21. At the end of the plan year, PTO would reach the 120-hour cap, leaving 64 after the 56 booked.
How to use it
- Open the file in Excel, or in Google Sheets with File > Import.
- On Settings, set the plan year start and your pay frequency. "Balances as of" shows today; type a date to see another day.
- On Team, add each person with their hours per workday, accrual per pay period, carryover and any cap. Leave the cap blank for none. If you give sick time up front, put it under "carried over or granted" and set its accrual to 0.
- Check the Holidays sheet: delete the federal holidays you don't give and add your own.
- Log time off in the Time off log. For part of a day, type the hours in "Hours if different".
- Check Balances. If your payroll counts pay periods differently, type the right number over "Pay periods completed" on Settings.
The formulas, explained
- Pay periods completed
-
=IF(B5="Biweekly",INT((B7-B3+1)/14),…)Counts whole pay periods from the plan year start to the "as of" date: every 7 days for weekly pay, 14 for biweekly, two a month for semimonthly (from the 1st and the 16th, so it assumes the plan year starts on the 1st) and one a month for monthly. It never goes past the number in a year.
- PTO accrued so far
-
=MIN(cap, carryover + periods × hours per period)Carryover plus what has accrued, held at the cap if there is one.
- Hours counted
-
=NETWORKDAYS(C4,H4,Holidays!$B$4:$B$60)*hours per workdayWeekdays between the first and last day, less company holidays, times the person's hours per workday. "Hours if different" overrides it. If the person has no hours per workday on Team, it says so instead of counting 0.
- PTO balance
-
=accrued so far − used or bookedWhat is left after everything booked this plan year.
Every formula uses functions that Excel, Google Sheets and LibreOffice all have. There are no macros or scripts.
What the law says
United States
Federal law doesn't require paid vacation, sick leave or holidays: the Fair Labor Standards Act "does not require payment for time not worked, such as vacations, sick leave or federal or other holidays". Those are matters of agreement between employer and employee. There is no federal requirement for paid sick leave, but for companies subject to the Family and Medical Leave Act it requires unpaid sick leave for eligible employees. Some states have their own paid sick leave laws: California, for example, generally requires 5 days or 40 hours a year. Check with your state labor office. The tracker applies the cap simply: accrual stops when carryover plus accrual reaches it. Payroll systems often apply a cap each pay period, so a balance that has been used can grow again. Check against your payroll.
Each statement comes from the official sources at the end of this page, checked 4 October 2026.
Questions
Is PTO required by law in the US?
Not under federal law. The Department of Labor says the FLSA doesn't require payment for vacations, sick leave or holidays. Some states require paid sick leave (California, for example), so check with your state labor office.
How is accrual per pay period worked out?
Divide the yearly hours by the number of pay periods. 104 hours a year paid biweekly (26 periods) is 4 hours per pay period.
How do I track part of a day?
Type the hours in "Hours if different" in the Time off log. That number is used instead of full workdays.
How does the cap work?
Accrual stops once carryover plus accrual reaches the cap. Leave the cap blank for no cap.
Why is sick time separate?
Many employers keep sick time apart from vacation, and state paid sick leave laws, like California's, apply to sick time. So it has its own accrual, carryover and cap.
When should I move to software?
When several people edit the file, when you need approvals, or when balances must match payroll every pay period.
Sources
Last reviewed 4 October 2026. We link to the regulator, the law or the platform's own help pages.
- US Department of Labor: vacation leave
- US Department of Labor: sick leave
- US Department of Labor: state labor offices
- California Labor Commissioner: paid sick leave FAQ
- US Office of Personnel Management: federal holidays
Changes: 4 October 2026: first published.
Free to use and change in your own business. Please don't resell the files or publish them as your own. More free templates.