A simple Google Sheet Template that can be used as a vacation tracker and PTO vacation accrual calculator.
This Google Sheet template can also be used to track and calculate Employee’s PTO (or leave or vacation) accrual balances.
This template is designed for Google sheets, but if you are looking for an Excel template, please visit PTO Balance Calculator (Salaried)
Benefits of Google Sheets
- 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.
Free Google Sheets
How to Use the PTO Calculator Salaried Google Sheet template
- Make a Copy and save this template in your Google Drive.
- Configure the input entries.
- Review the PTO policy and first accrual window details
- Enter PTO info
- View Balance and Balance Trend
Step 1: Make a Copy
Click on the link above and then make a copy of the sheet as shown in the screenshot below.
You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed.
Step 2: Configure the Input Entries
Step 3: Review the PTO policy and first accrual Window details
Step 4: Enter PTO info
Step 5: View PTO Balance and Balance Trend
Components of the 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.
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.
Let’s see how a Monthly scenario would work.
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.
In this example, the employee’s hire date is Jan 1st, 2020. 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, 2020 (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 ‘End of accrual period’. So, the employee receives the PTO accrued on 7th 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.
The Policy shows that the employee will accrue 2.308 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 7th Jan. The amount on that day will be 2.308 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.
We can see that the first valid accrual window is still 1st Jan to 7th Jan, and the accrual happens in 7th Jan (end of window). However, the amount if only 1.648 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.
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.
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:
- Employees are encouraged to take regular time off to maintain a healthy work-life balance.
- 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.
- Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
- Rollover Limit: We can set a limit on how many hours are carried over.
- 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.
To see this in action, I have chosen a 40 hour roll over limit.
At the end of the year, the employee’s PTO balance comes from 120 to 50. It is 50 because 40 is carried over and the employee accrues 10 hours for January of next year.
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.
The next section covers the remaining options in PTO policy.
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.
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.
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.
PTO OR VACATION DATES
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 3 days (June 2, June 3 and June 4) – in total 9 hours – will be subtracted from the PTO balance. 2 hours will be subtracted on 5th July.
You can enter more vacations by just typing new row of data in the table.
PTO BALANCE
As we enter PTO dates, the balances get updated.
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
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).
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. We don’t want you 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 2019. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2020, 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.
Though the employee starts on Jan 1st, 2020, we have entered Dec 1, 2019 as First Accrual Period Begin date.
Employee is eligible to accrue from Jan 1, 2016, but the first accrual window is Dec 1, 2019 to Dec 7, 2019. 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.
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.
