Free Excel template · Excel for hr and people ops

Free PTO tracker template

Q: How do I prorate PTO for a new hire?

Count only the pay periods since the hire date: =MIN(periods_this_year,INT((as_of-hire_date)/14)) for biweekly pay, then multiply by the rate. Your policy may also have a waiting period; add it as an input if so.

For HR teams and office managers who track paid time off outside the payroll system, or want to check it. Enter your accrual policy in the table (hours per pay period by years of service), each employee's hire date, carryover and hours used. The template works out years of service, the accrual rate, hours accrued so far this year (prorated for new hires), the balance in hours and days, and flags negative balances and anyone at your cap.

cellwise-pto-tracker-template.xlsxExcel workbook · 4.9 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I	J	L
1	PTO tracker
2	As of	Oct 31, 2026
3	Pay periods so far this year	22
4	Hours in a workday	8
5	Balance cap (hrs, 0 = none)	160
6
7	Employee	Hire date	Years of service	Accrual per pay period (hrs)	Carryover (hrs)	Accrued this year	Used this year	Balance (hrs)	Balance (days)	Note	Years of service from
8	E-201	Mar 17, 2014	12	7.69	40	169.18	96	113.18	14.1		0
9	E-202	Jun 3, 2019	7	6.15	24	135.3	72	87.3	10.9		2
10	E-203	Sep 12, 2022	4	4.62	16	101.64	40	77.64	9.7		5
11	E-204	Feb 24, 2025	1	3.08	0	67.76	32	35.76	4.5		10
12	E-205	Nov 6, 2017	8	6.15	40	135.3	120	55.3	6.9
13	E-206	Apr 10, 2023	3	4.62	8	101.64	88	21.64	2.7
14	E-207	Mar 2, 2026	0	3.08	0	52.36	60	-7.64	-1	Negative balance
15	E-208	Aug 17, 2020	6	6.15	32	135.3	56	111.3	13.9
16	E-209	Jan 24, 2011	15	7.69	40	169.18	40	169.18	21.1	At cap
17	E-210	Jul 8, 2024	2	4.62	12	101.64	48	65.64	8.2
18
19	Total balance (hrs)							729.3
20	Total balance (days)							91.2

How the formulas work

Years of service

C8=DATEDIF(B8,$B$2,"y")

Complete years from the hire date to the as-of date in B2. Using a typed as-of date rather than TODAY() means the balances stay fixed for the period you are reporting on.

Accrual rate from your policy table

D8=VLOOKUP(C8,$L$8:$M$11,2,TRUE)

VLOOKUP with TRUE finds the highest "years of service from" value that is not above the employee's years, so 6 years lands on the 5-year row. The sample rates (3.08 hours per pay period is about 80 hours a year on 26 pay periods) are placeholders: replace them with your policy.

Accrued this year, prorated for new hires

F8=ROUND(D8*MIN($B$3,INT(($B$2-B8)/14)),2)

Rate times pay periods. MIN uses whichever is smaller: the pay periods so far this year (B3), or the full two-week periods since the person was hired, so someone who started in March only accrues for the periods they worked. If you pay weekly or twice a month, change the 14 and B3 to match.

Balance and alerts

J8=IF(H8<0,"Negative balance",IF(AND($B$5>0,H8>=$B$5),"At cap",""))

The balance in H is carryover plus accrued minus used. This note flags a negative balance (someone used time before earning it) and anyone at or over the cap in B5. Set B5 to 0 if your policy has no cap.

Make it yours

This template uses the tenure tier on the as-of date for the whole year. If someone moves up a tier mid-year, split their year into two rows or adjust Accrued this year by hand.
Log PTO taken in a separate sheet (date, employee, hours) and total it here with =SUMIF(Log!B:B,A8,Log!C:C) instead of typing Used this year.
Prefer to see days? Column I divides hours by the workday length in B4. Change B4 for part-time staff on a separate copy of the sheet.
Check it against payroll once a quarter. Differences usually come from mid-year tier changes, unpaid leave, or carryover rules.

FAQ

How do I calculate PTO accrual in Excel?

Multiply the accrual rate per pay period by the number of pay periods worked, then add carryover and subtract hours used. To vary the rate by tenure, look it up from a policy table with VLOOKUP or XLOOKUP.

How do I prorate PTO for a new hire?

Count only the pay periods since the hire date: =MIN(periods_this_year,INT((as_of-hire_date)/14)) for biweekly pay, then multiply by the rate. Your policy may also have a waiting period; add it as an input if so.

What accrual rate should I use?

Whatever your written PTO policy says. The rates in the template are examples. Leave and payout rules also differ by location, so check them before you set a policy.

Functions used

Related guides

More free templates

Updated November 2, 2026