PTO Accrual in Excel: Track Hours Earned, Used and Left

PTO accrual in Excel comes down to one line: hours earned minus hours used, plus anything carried over. PTO means paid time off, and accrual means it builds up a little each month or pay period instead of arriving all at once. This guide builds a small tracker with DATEDIF for hours earned, SUMIFS for hours used and MIN for a balance cap. It suits HR and payroll staff, managers and anyone checking their own balance.

How PTO accrual works#

An accrual policy sets how fast PTO builds up and what limits apply. The rules come from your employer's policy and local law, so check them before you build anything. The figures in this guide are examples only, chosen to keep the maths easy:

Other common methods are a fixed amount per pay period, or a fraction of an hour for every hour worked. The sections below show the formula for each.

Step 1: Set up the inputs#

Put every policy number in its own labelled cell, so the formulas refer to cells, not typed numbers.

Enter the dates as real dates, not text, or the date maths will fail. If the rate changes next year, you change one cell and every formula updates.

Step 2: Calculate hours earned#

For a monthly rate, count the full months worked with DATEDIF, then multiply by the rate. In B6:

=DATEDIF(B1,B2,"m")*B3

From 15 January to 31 October is 9 complete months, so the employee has earned 9 × 8 = 72 hours. The "m" unit counts only full months, which matches a policy that credits PTO after each complete month. DATEDIF doesn't appear in Excel's formula suggestions, but it works in every version. The DATEDIF function guide lists all its units, and the article on calculating age and tenure uses the same function for years of service.

Per pay period#

If PTO accrues each payday, divide the yearly hours by the number of pay periods. For an example of 80 hours a year, paid every two weeks (26 pay periods):

=ROUND(80/26,2)

That gives 3.08 hours per pay period. Count the paydays so far and multiply. With a first payday of Friday 9 January 2026, =INT((B2-DATE(2026,1,9))/14)+1 returns 22 paydays by 31 October, so 22 × 3.08 = 67.76 hours.

Per hour worked#

Some policies give, for example, 1 hour of PTO for every 40 hours worked. With hours worked in B8:

=ROUNDDOWN(B8/40,2)

For 1,412 hours worked, that is 35.3 hours of PTO. ROUNDDOWN avoids giving away a fraction of an hour that hasn't been earned yet. Round the same way every time, and match what your payroll system does.

Step 3: Log the time taken#

Keep one row per absence in a leave log, with the same columns every time:

Record hours, not days, so half days and short days add up correctly. Use the same spelling for names and types; a drop-down list stops "pto" and "P.T.O." creeping in. In a real tracker, add a date column so you can filter by year. The employee leave tracker guide shows how to count working days for each request with NETWORKDAYS.

Step 4: Total hours used with SUMIFS#

SUMIFS adds the hours in rows that match every condition you give it. To total Lina's PTO, and not her sick time:

=SUMIFS(C2:C7,A2:A7,"Lina",B2:B7,"PTO")

The result is 20 (8 + 4 + 8). The sum range comes first, then each range with its condition. To report on one year only, add a date column and two more conditions, like D2:D7,">="&DATE(2026,1,1). The SUMIFS function guide covers the syntax, and the same pattern totals hours on a timesheet.

Step 5: Work out the PTO balance#

The balance is carryover plus earned minus used. With earned in B6 and used in B7:

=B5+B6-B7

That gives 16 + 72 − 20 = 68 hours. To show it in days, divide by the length of a workday: with 8-hour days, =B9/8 (balance in B9) returns 8.5 days.

To apply the cap, you might try =MIN(B4,B5+B6-B7). That is only right if the balance never reached the cap during the year. Under most capped policies, accrual stops while the balance sits at the cap, so hours "earned" during that time are never added. A month-by-month balance handles this correctly.

Track the balance month by month with a cap#

A running balance applies the cap each month, the way a capped policy works. Put one row per month, and in the Balance column use:

=MIN($B$4,D2+B3-C3)

Here D2 is the previous month's balance, B3 the hours earned this month and C3 the hours used. $B$4 is the cap, locked so it stays fixed as you fill down. Using an example cap of 40 hours and an opening balance of 24:

In May, 40 + 8 would be 48, so MIN holds it at 40. The true June balance is 32. A single MIN on the half-year total gives 24 + 48 − 32 = 40, capped to 40, which overstates it by 8 hours: the hours "earned" in May while the balance sat at the cap are counted after all.

Common PTO accrual mistakes#

Most wrong balances come from a handful of slips:

1. Counting part months. DATEDIF with "m" counts complete months only. If your policy credits PTO on the 1st of each month, count calendar months instead.
2. Rounding each row differently. Decide on ROUND or ROUNDDOWN and the number of decimals once. The payroll calculator guide shows why rounding every line the same way keeps totals right.
3. Mixing hours and days. Keep everything in hours and convert to days only for display.
4. Text dates. A date typed as text makes DATEDIF return #VALUE!.
5. Counting sick days as PTO. Always add the Type condition to SUMIFS.

These are core skills for HR and payroll roles; the article on Excel skills for payroll jobs lists the rest.

Practise it#

A PTO tracker is three formulas, and each one is a skill you can practise. Practise SUMIF in a live spreadsheet to total hours by condition, practise days between dates to count months of service, and practise rounding so accruals match payroll. Each lesson checks your formula instantly. Start with the exercise on this page.