Indzara

PTO Tracker & PTO Calculator 2024 – Free Excel Template

Do you want to easily find out how many days of PTO (Paid Time Off) you have available? Wondering if there is a simple spreadsheet that can be used as vacation tracker and PTO vacation accrual calculator?

You have come to the right place – Employee PTO Tracker Excel Template.

You can download this free excel pto tracker template to track and calculate Employee’s PTO (or leave or vacation) accrual balances.

Employee PTO (Paid Time Off) Calculator – PTO Balance

This free PTO Tracker excel template is designed to calculate PTO balances where PTO is accrued based on tenure. If you are looking for a PTO calculator for hourly employees where PTO is accrued based on hours worked by employee, please visit PTO Calculator (Hourly Employees).

If you would like to manage PTO for multiple employees, please visit Small Business PTO Manager Excel template.  

Free Download

Video Demo

How to track PTO accrual and balances in Excel? – Overview

Enter inputs in the Employee PTO sheet

Monthly Accrual PTO Calculator – Inputs to Template

Review the PTO policy and first accrual window details

Monthly Accrual PTO Calculator – Review PTO Policy

Fix if there are any data validation errors.

When employee takes PTO, enter PTO info

Enter Vacation rates in PTO Calculator

View PTO balance and balance trends

View PTO Balance calculated by Template

Key Benefits of PTO Accrual Calculator in Excel

  • Several settings available to cover most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates PTO balances for today and any future date
  • Vacation dates can be entered as date ranges
  • File is designed for one employee only. Make a copy of the workbook to use for the second employee.

How to track PTO accrual and balances in Excel? – In Depth

Components of PTO Policy

Though the template is very simple to use, there are quite a few terms to understand and several calculations that happen behind the scenes. Let’s start from the beginning. Let’s start with the simple terms first.

User Inputs on PTO Policy

EMPLOYEE NAME
This does not need any explanation. Enter name of employee for whom we will be tracking and calculating PTO balance.

HIRE DATE
A lot of the calculations for employee’s PTO balance depends on the Hire date of employee. Just enter Hire date. Even if you have been tracking PTO using some other tool and now want to use this template, enter the actual hire date of the employee. Tenure (how long an employee has been with the organization) is calculated from the hire date and companies may have tenure based increase in PTO.

PTO UNIT
We can choose to track employee PTO in units of days or hours. If we choose Hours, we have to enter PTO taken by employee in Hours. If we choose Days, we can just enter PTO dates (which we will discuss later) and ignore hours taken off.

ANNUAL PTO ACCRUAL RATE
Annual Accrual Rate is the PTO that an employee accrues in one year. For example, a company may offer 120 hours of PTO per year.

PTO ACCRUAL PERIOD
This is to inform how we accrue the annual PTO rate. Continuing with the above example of 120 hours per year, how will the employee receive these 120 hours. We have 6 options here: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.

PTO Accrual Period Options - Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual
PTO Accrual Period Options – Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual

Let’s see how a Monthly scenario would work.

PTO Accrual Frequency and Annual PTO Accrual Rate

120 hours will be given to the employee at 10 hours each month for 12 months.

FIRST ACCRUAL PERIOD BEGIN DAY and ACCRUAL TIMING
In order to discuss the next two terms, we need to take an example. Let me use a Weekly accrual example to demonstrate.

Weekly PTO Accrual Example Inputs for Template

In this example, the employee’s hire date is Jan 1st, 2024. Employee’s Annual PTO accrual rate is 120 hours and that is accrued weekly.  When we think of accrual periods, we have to think of a window with a start date and an end date.
Let’s enter First Accrual Period Begin Date as Jan 1st, 2024 (this is user input). So, the first accrual period window will be 1st Jan to 7th Jan.
When does the employee receive the accrued PTO? Is it on 1st or 7th? This can be controlled easily. In the above example, we have chosen ‘Beginning’. So, the employee receives the PTO accrued on 1st Jan.

We don’t have to remember all these calculations because that’s why we use such a PTO calculator tool. 🙂  Let’s review the policy as calculated by the template.

Weekly PTO Accrual Example – Review Policy

The Policy shows that the employee will accrue 10 hours per week. The first accrual window is 1st Jan to 7th Jan. First accrual day where PTO will be awarded to the employee is 1st Jan. The amount on that day will be 10 hours. This amount is the same as the weekly rate, because the employee starts on 1st Jan and the weekly window also begins on 7th.
We all know that employees can start in a new job on any day. So, let’s take the same example but for an employee who started on 3rd Jan.

Weekly PTO Accrual Example where Employee Starts in the middle of accrual window

We can see that the first valid accrual window is still 1st Jan to 7th Jan, and the accrual happens in 3rd Jan (start of employment).  However, the amount if only 7.143 hours because the employee only accrues for 5 days (3rd Jan to 7th Jan). Thus the template can easily prorate the PTO awarded when an employee joins in the middle of an accrual window.

The approach is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. Twice a Month and Monthly are slightly different.

TWICE A MONTH

For Twice a Month, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days.

Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’

The template will then take those two days as the accrual days every month. You can choose ‘Last day’ for the second day and the template can automatically assign the last day of each month, whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

MONTHLY

For Monthly, we don’t need to provide First Accrual Period Begin Date. Instead we will choose a day of Month. The options are 1 to 28 and Last day.

Monthly PTO Accrual – Input day of month – First day example

The ‘Last day’ will be accounted for, correctly whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

Now, let’s look at some more options we have with setting PTO/Vacation policy.


ANNUAL PTO ROLLOVER POLICY
As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no vacations taken. Typically, companies do not want employees to accrue a very large balance. Two reasons:

  1. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
  2. Companies may consider remaining PTO balance as cash that needs to be paid to employee if employee leaves the company. So, very high balance could mean more cash out the door for the company. So usually, there is a rollover policy. This determines how many hours of PTO can the employee carry over from one year to the next year.

The template allows three possibilities.

PTO Rollover Policy Settings - Zero Rollover, Rollover Limit, Unlimited Rollover
PTO Rollover Policy Settings – Zero Rollover, Rollover Limit, Unlimited Rollover
  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. Unlimited Rollover: Here the employee does not lose any PTO, and will carry over everything to next year. This is an unusual policy for a company.

Now with this rollover policy, there is another variation. Companies may apply rollover at calendar year change or on work anniversary dates. You can easily change that setting.

PTO Rollover timing can be Calendar year or Work Anniversary
PTO Rollover timing can be Calendar year or Work Anniversary

The next section covers the remaining options in PTO policy.

More PTO policy settings and options - Probationary period, Maximum allowed PTO and tenure based PTO
More PTO policy settings and options – Probationary period, Maximum allowed PTO and tenure based PTO

PROBATIONARY PERIOD
In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee does not earn any PTO during the first 30 days of employment. You can set that easily in this template.

MAXIMUM ALLOWED PTO BALANCE
The rollover limit only applies to the end of the year balance. Some companies can set a limit on maximum balance at any time. We can set the amount in the Maximum Allowed PTO Balance.

ACCRUAL RATES VARY BY TENURE
Companies increase the annual accrual rate for employees who stay with the company for more years. We can handle such scenarios as well. We would choose YES to this first and then fill out the table below.

Employee Vacation Accrual Template - Annual Rate increased by Tenure
Employee Vacation Accrual Template – Annual Rate increased by Tenure

We can set the Annual PTO Accrual rate and Maximum PTO balance. In the example above, the employee will receive at the rate of 56 hours in the first year, then rate of 106 hours in the second and third year, 144 hours in years 4 to 10.

Important: Please make sure that the first entry here is for 0 completed years.

You can enter more rows as needed.  Read how to enter and delete data in Excel tables

Now we have gone through the various input options in the PTO calculator. These settings have to be entered only once for an employee. After these are finalized, we will enter PTO dates whenever an employee is taking vacation.

Entering PTO or Vacation Dates

Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day

If we track PTO in hours, we have to enter the PTO hours column. We can ignore it if our PTO unit is days. We can enter date ranges to enter multi-day vacation. However, if it is a single day vacation, please enter both start and date as the same date.
In the above example, 3 hours of PTO for each of the 2 days (Jan 2, Jan 3 ) – in total 6 hours – will be subtracted from the PTO balance.


You can enter more vacations by just typing new row of data in the table.

Viewing PTO Balance

As we enter PTO dates, the balances get updated.

Current PTO Balance shown by default and PTO Balance shown from Hire Date

By default, today’s PTO balance is shown at the top. You can modify the date and can view PTO balance any date. To put it back to today, just type =TODAY().


Similarly, the balance trend chart shows data by default from Hire Date of Employee. You can edit and modify that as well.
Enter the number of days to control the duration displayed in the chart

Entering PTO Adjustment

If you would like to add or remove PTO, outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

Make positive and negative adjustments to PTO Balance easily

An example would be an employee who has been with the company for a few years. You were using some system to track the PTO balance and now you want to migrate to this template. You don’t have to enter all the vacation dates from the past. You can just enter the adjustment amount to bring the current balance to the correct amount. If the employee has taken 60 hours of PTO already, then enter -60 as adjustment.

Prorating when accrual rate changes

As we had discussed earlier, the accrual rate can vary by employee tenure. If the work anniversary happens to be in the middle of an accrual window, then we have to prorate the PTO accrued.

Let’s take an example where an employee’s hire date is Jan 16th 2023. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2024, he will earn 15.16 hrs. 15 days at the rate of 10 hrs per month and 16 days at the rate of 20 hrs a month.

The template does this prorating calculation by default.

Data Validations

When you enter the First Accrual Period Begin Date, if it is earlier than or after the first valid accrual window, an error will appear. Let’s look at an example.

Data Validations built in the template – Example Inputs

Though the  employee starts on Jan 1st, 2024, we have entered Dec 1, 2023 as First Accrual Period Begin date.

First Accrual Period Begin Date is too late

Employee is eligible to accrue from Jan 1, 2024, but the first accrual window is Dec 1, 2023 to Dec 7, 2023. If the employee will not accrue any balance from Jan 1st to Dec 1st, it is incorrect. This is due to the data entry error. Once we update the First Accrual Period Begin Date correctly, error will go away.

Recommended Premium Template

Small Business PTO Manager for Salaried employees – Manage multiple employees’ data in one file.


I would like to hear your feedback. Have I missed any of the scenarios that happen in your workplace? Do you find this useful? Please leave your comments below. Please share with your friends.

221 Comments

  • How do I change the number of accrual periods per year? I have it set to 120 hours Annually but under PTO policy it still Shows 12 Accrual Periods at 10 hours when it should be 1 @ 120 hours.

    Reply
    • We regret the inconvenience caused.

      I have gone through the sheet and found the formula in the cell I8 has been removed. Please paste the below formula in cell I8 to calculate the number of accrual periods according to the the accrual period selected in the input cell C12:
      =COUNTIFS(Table3[ACCRUAL DAY],TRUE,Table3[DT],”>=”&I_HIREDT,Table3[DT],”<="&DATE(YEAR(I_HIREDT)+1,MONTH(I_HIREDT),DAY(I_HIREDT)-1)) Best wishes.

      Reply
  • Hi – Can the templete be adjusted for 4 day work week that is different for each employee to track days worked? Some employees work Mon-Thurs and others Tues-Friday.

    Reply
  • Is this available as a google template? the link won’t open for me, and this is exactly what i’m looking for!

    Reply
  • Hi there! I love your template, however I have a question. I’d like to add a custom date range to the Rollover Timing drop down as my pto is tracked on a fiscal year end, not the calendar year nor my start date. How do I do that?

    Reply
  • Hi I downloaded your template for PTO Calculator. I imputed my pertinent information on my employee but the PTO balance hours on the far right chart is not adjusting to show current based on info I imputed. It still shows 350 hours. Can you please advise how to fix this.

    Also, how do I remove your example PTO dates which were entered on your example so the chart reflects info correctly.

    Reply
    • Thank you for showing interest in our template.

      We are unable to replicate the highlighted issue. Hence please share your copy of the template at the below link to assist further:
      https://support.indzara.com/support/tickets/new

      Regarding the example data,

      You just need to select the data and delete the same.

      Best wishes.

      Reply
  • I have about 100 employees, could I have the template for multiple employees? Also, is there a template that tracks both Vacation & Sick separately?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *