How do I calculate sick leave accrual?

Divide the hours you have worked by your accrual ratio. At 1 hour per 30 worked, 1,040 hours gives 34.67 hours of sick leave. Apply any yearly cap your employer or state sets.

How much sick leave do I earn at 1 hour per 30 hours worked?

You earn 3.33 percent of the hours you work. A full time year of 2,080 hours produces 69.33 hours, which is about 8.7 days. Most employers cap this at 40 or 48 hours.

How many sick days is 40 hours?

Forty hours is 5 days on a standard 8 hour schedule. On a 10 hour shift it is 4 days, and on a 12 hour shift it is 3.33 shifts.

Why did my sick leave stop building up?

You have probably hit a yearly accrual cap. Many sick leave laws let employers stop accrual at 40 or 48 hours a year, which a full time employee reaches partway through the year.

Does sick leave carry over to next year?

Usually yes where a sick leave law applies, but often with a cap on how much moves forward. Employers who front load the full amount each year generally do not have to carry anything over.

Do part time employees earn sick leave?

Yes, at the same rate per hour worked. Someone on 20 hours a week earns 34.67 hours a year at 1 per 30, compared with 69.33 for a full time employee.

Is sick leave paid out when I leave a job?

Usually not. Most states that require paid sick leave do not require it to be paid out on termination, which is different from vacation. Some employers pay it voluntarily.

What is the difference between accrued and front loaded sick leave?

Accrued sick leave builds as you work. Front loaded sick leave is granted in full at the start of the year. Both are permitted under most sick leave laws, as long as the front loaded amount matches what accrual would give.

What does pro-rata mean?

Pro-rata means in proportion. You receive a share of a full amount based on how much of the period you cover. Twelve days of a 24 day allowance if you work half the year.

What does prorated PTO mean?

Prorated PTO is paid time off adjusted for the part of the year you are covered. It applies when you join mid year, leave mid year, or work part time.

How do I calculate prorated PTO?

Multiply your full annual entitlement by the share of the year you cover. For 15 days over 6 months, that is 15 multiplied by 6 divided by 12, which is 7.5 days.

How do I calculate pro-rata pay?

Divide your weekly hours by full time hours and multiply the full time salary by that fraction. Twenty four hours against a 40 hour week on a $50,000 salary gives $30,000.

Does pro-rata mean part time?

Not always. Pro-rata describes any proportional share. It applies to part time roles, but also to anyone joining or leaving part way through a leave year on full time hours.

Do employers have to round prorated leave up?

No, unless your policy or contract says so. Rounding up to the nearest half or whole day is common practice rather than a legal requirement.

Is prorated the same as pro-rata?

They mean the same thing. Prorated is the usual American spelling and pro-rata is more common in British and Commonwealth usage, especially in employment contracts.

What happens if I used more leave than my prorated entitlement?

Some employers deduct the excess from your final pay. Whether they can depends on your state and whether your contract allows it. Ask HR for the clause in writing.

How do I calculate vacation accrual?

Turn your yearly entitlement into hours, then divide by your number of pay periods. Two weeks for someone on a 5 day, 8 hour schedule is 80 hours, which is 3.08 hours per biweekly payday.

How many hours is 2 weeks of vacation?

Two weeks is 80 hours for someone working 40 hour weeks, or 10 working days. On a 37.5 hour week it is 75 hours.

How many hours is 3 weeks of vacation?

Three weeks is 120 hours on a standard 40 hour week, which is 15 working days. On four 10 hour days a week it is still 120 hours but only 12 shifts.

How do I calculate vacation hours per pay period?

Divide your yearly vacation hours by your pay periods. Eighty hours across 26 biweekly periods gives 3.08 hours. Across 24 semi-monthly periods it gives 3.33 hours.

How is vacation prorated if I start mid year?

Multiply your full entitlement by the months you are eligible, divided by 12. Someone joining in July with 15 days gets 7.5 days for that first year.

Does vacation accrual carry over to next year?

It depends on your employer and your state. Some carry the full balance, some cap it, and some reset it to zero. A few states restrict resetting earned vacation because they treat it as wages.

What is the difference between an accrual cap and use it or lose it?

A cap stops you earning once your balance reaches a ceiling, at any time of year. Use it or lose it wipes your balance at year end. A cap keeps what you have, a reset does not.

Do vacation days increase with years of service?

At most employers, yes. Two weeks in the early years, three weeks after around five, and four weeks after around ten is a common ladder. Your handbook sets the exact steps.

How do I calculate vacation pay?

Multiply your unused vacation hours by the pay rate your employer must use. Eighty hours at $25 an hour is $2,000 before tax. Check whether shift differentials or bonuses raise that rate.

Is my employer required to pay out unused vacation?

There is no federal requirement. It depends on your state and your written policy. California, Colorado, Montana, and Nebraska treat accrued vacation as earned wages that must be paid. Most other states follow the employer's policy.

What rate is vacation paid out at?

Usually your final base rate rather than the rate when the days were earned. Shift differentials and non discretionary bonuses often have to be included. Overtime premiums normally do not.

Do commissions count toward my vacation pay rate?

For commissioned staff, usually yes, averaged over a recent period. How that average is worked out varies, so check your policy.

Does a recent pay rise change my payout?

At most employers, yes. Payouts are normally calculated at your final rate, so a raise before you leave increases what you are owed.

Can my employer pay out vacation at less than full value?

Some do where state law allows it and the policy says so. In states that treat vacation as earned wages, they generally cannot.

What if I was fired rather than resigned?

The payout rules are often the same, but some policies withhold it for dismissal with cause or where notice was not given. Your state law may override that.

How long does my employer have to pay me?

Each state sets a deadline for final pay, ranging from your last day of work to the next scheduled payday. Vacation pay normally sits inside that final cheque.

How do I convert PTO hours to days?

Divide your PTO hours by the number of hours in your normal workday. On an 8 hour schedule, divide by 8. Eighty hours becomes 10 days.

How many days is 80 hours of PTO?

Eighty hours is 10 days on an 8 hour schedule and 2 full work weeks. On a 7.5 hour day it is 10.67 days. On a 10 hour day it is 8 days.

How many days is 40 hours of PTO?

Forty hours is 5 days on an 8 hour schedule, which is one full work week. On a 10 hour day it is 4 days.

How many hours is 2 weeks of PTO?

Two weeks is 80 hours for someone working 40 hour weeks. On a 37.5 hour week it is 75 hours.

How many days is 120 hours of PTO?

One hundred and twenty hours is 15 days on an 8 hour schedule, or 3 work weeks. On a 12 hour shift it is 10 shifts.

Why does my employer count PTO in hours?

Hours let payroll handle part days and staff on different shift lengths. A half day off is simply 4 hours, and one hours figure works for full time, part time, and shift staff alike.

Can I take PTO in less than a full day?

It depends on your policy. Some employers allow one hour blocks, others require half days or full days. Your handbook sets the minimum increment.

How do I convert PTO days back into hours?

Multiply your days by your workday length. Ten days on an 8 hour schedule is 80 hours.

How does a PTO accrual calculator work?

It divides your yearly PTO hours by your number of pay periods. Eighty hours a year paid every two weeks gives 3.08 hours per period. If you accrue per hour worked instead, it divides your hours worked by your accrual ratio.

How much PTO do I earn per pay period with two weeks a year?

Two weeks is 80 hours. Paid every two weeks that is 3.08 hours per period. Paid twice a month it is 3.33 hours. Paid monthly it is 6.67 hours.

What is a normal PTO accrual rate?

Two weeks a year is common for new employees in the United States, which is 80 hours. Many employers raise this with length of service, reaching three or four weeks after several years.

How does PTO accrual work for hourly employees?

You earn PTO as you work. A common rate is one hour of PTO for every 30 or 40 hours worked. Work 80 hours at a 1 per 30 rate and you earn 2.67 hours of PTO.

Free Excel PTO Tracker Template (Multiple Employees)

Most small businesses outgrow a single shared spreadsheet before they’re ready to pay for payroll software. Somewhere between five and twenty employees, a plain list of names and vacation days stops being enough.

You need accrual that updates on its own, a cap that stops a balance from growing past what your policy allows, and a place to see everyone’s numbers at a glance without opening ten different tabs.

This excel pto tracker template sits in exactly that gap. Enter each employee’s annual PTO and sick leave entitlement once, and the sheet works out monthly accrual, running balances, and caps for you.

It’s a real Excel file, not a locked demo. Every number you see is a formula, so if you change an entitlement or add a new hire, the whole sheet updates with it.

Free Excel PTO Tracker Template

Download the free template using the button above.

Who this pto tracker excel template is for

If you’re running payroll software already, most of it does this for you and you don’t need this. This template is built for the businesses in between: too big for a shared note or a paper log, not yet running a system that tracks leave automatically.

It also works well as a backup record alongside a payroll system, since it gives you a second copy of every employee’s balance that isn’t locked inside someone else’s software.

It assumes you’re comfortable opening a file in Excel or Google Sheets and typing numbers into cells. Nothing beyond that. You don’t need to write a single formula, and nothing in the sheet needs code or a plugin to run.

What’s inside the pto excel template

The workbook has six tabs, and you’ll only ever type into two of them regularly.

Cover. A one-page summary of what the workbook does and where to go first.
Instructions. The full color key, a walkthrough of each tab, and a plain list of what this template assumes about how your business accrues leave.
Setup. One row per employee. Enter their name, start date, employment type, and annual PTO and sick leave entitlement in hours. The monthly accrual rate and the annual cap calculate automatically from that.
PTO Log. A twelve-month block for each employee. Pick their name from a dropdown, then enter hours used each month. Beginning balance, hours accrued, and ending balance fill in on their own.
Sick Leave Log. The same layout as the PTO Log, kept as a separate tab, since most employers accrue and cap sick leave differently from PTO.
Summary Dashboard. A one-line view per employee: current PTO balance, current sick balance, a color-coded status showing whether either has gone over its cap, and a chart comparing balances across the whole team.

How to use this pto tracker excel template

Step 1

Start on the Setup tab and fill in each employee’s details and annual entitlements. This is the only tab where you’re entering information that doesn’t change month to month.

Step 2

Move to the PTO Log and Sick Leave Log tabs. Select the employee’s name from the dropdown at the start of their twelve-month block, so the sheet knows whose accrual rate and cap to pull in.

Step 3

Each month, type in the hours they used. Leave the cell blank if they didn’t take any that month. There’s nothing else to fill in on these rows.

Step 4

Check the Summary Dashboard whenever you want a running total across everyone, including a quick visual flag for anyone who’s gone over their cap.

The template comes with one example employee, Jane Doe, already filled in with a full year of sample data. Look at how her numbers move through the year before you clear the row and add your own team. It’s the fastest way to understand the pattern without reading the full instructions first.

Why PTO and sick leave are tracked separately

A lot of free trackers lump PTO and sick leave into one running number, and it works right up until it doesn’t. An employee might accrue sick leave faster than PTO, or their sick leave cap might sit lower than their PTO cap.

Once that happens, a single combined balance can’t tell you which type of leave they actually have left. Keeping PTO and sick leave on separate tabs, with separate accrual rates and separate caps, keeps every balance you see accurate to what’s actually owed under your policy.

If you want to see the accrual math this template is built on written out in full, our sick leave accrual calculator walks through why full-time accrual under a common one-hour-per-thirty-worked policy often outpaces a 40 or 48 hour statutory cap well before the year ends.

What this template doesn’t do

It assumes accrual happens evenly across twelve months. If your business accrues per pay period instead, convert your per-period rate to a monthly equivalent before you enter it.

For a straight month-by-month or day-by-day breakdown of a partial year, our pro-rata calculator handles that conversion for a single employee without needing a spreadsheet at all.

It ships set up for ten employees. To track more, copy a twelve-month block in each Log tab and a row in Setup, then copy the formulas down the new rows. The formulas are consistent across every row, so this is a copy-and-fill exercise rather than anything that needs rebuilding.

It doesn’t apply any state or country’s specific accrual, carryover, or payout laws. Those rules vary by location and change over time, so confirm your local requirements before relying on the caps you enter here.

If an employee is leaving and you need to work out what unused leave is worth in their final paycheck, our vacation pay calculator covers payout rules by state.

Frequently asked questions

Is there a free pto tracker excel template?

Yes. This one tracks multiple employees, calculates accrual automatically from each person’s annual entitlement, and separates PTO from sick leave. Download it above at no cost, no signup required.

How many employees can this pto excel template track?

It comes set up for ten. You can extend it further by copying a twelve-month block and a Setup row for each additional employee, then copying the existing formulas down.

Does it track PTO and sick leave together or separately?

Separately, on two different tabs. Most employers set different accrual rates and caps for each, and combining them into one number hides that difference until it causes a problem.

What if my company accrues leave per pay period instead of monthly?

Convert your per pay period accrual rate to a monthly equivalent, then enter that as the annual entitlement divided by twelve, or adjust the figure you enter to match. The formulas don’t care how you arrive at the number, only that it stays consistent from month to month.

Will this pto tracking spreadsheet template work in Google Sheets?

The formulas and conditional formatting will import. Check that the dropdown lists on the Log tabs still point to the Setup tab after uploading, since Google Sheets sometimes needs data validation ranges re-pointed after an Excel import.

Is this a replacement for payroll software?

No. It’s built for small teams that don’t run payroll software yet, or that want a simple backup record alongside one. Your payroll system’s balance is always the official one if the two ever disagree.

Can I use this pto excel template for a single employee instead of a team?

Yes. Just fill in the first row on Setup and the first twelve-month block on each Log tab, and leave the rest blank. The Summary Dashboard will only show a line for employees whose name appears in Setup.

Our other calculators

Pro-Rata Calculator works out a partial-year PTO or pay entitlement by months, exact days, pay periods, or part-time hours.
Sick Leave Accrual Calculator shows how sick leave accrues under common per-hours-worked policies, and where statutory caps kick in.
PTO Accrual Calculator covers accrual per pay period and per hour worked for a single employee.
Vacation Accrual Calculator tracks vacation earned across a full year for one employee.
Vacation Pay Calculator works out what unused leave is worth when an employee leaves, and which states require a payout.

Disclaimer

This template gives an estimate for information only. It is not legal, tax, or HR advice. Your payroll system holds the official balance, and accrual, carryover, and payout rules depend on your company policy and your state.