PTO Tracker & PTO Calculator 2025 – 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.
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
Review the PTO policy and first accrual window details
Fix if there are any data validation errors.
When employee takes PTO, enter PTO info
View PTO balance and balance trends
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.
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, 2025. 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, 2025 (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.
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.
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.
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.
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. 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
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.
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).
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 2024. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2025, 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, 2025, we have entered Dec 1, 2024 as First Accrual Period Begin date.
Employee is eligible to accrue from Jan 1, 2025, but the first accrual window is Dec 1, 2024 to Dec 7, 2024. 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.
Related Free Templates
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.
231 Comments
When I enter PTO dates they are not being applied to the balance tracker and graph.
We regret the inconvenience caused.
Please share your copy of the file with sample data having the highlighted issue at the below link to check further:
https://support.indzara.com/support/tickets/new
Best wishes.
Hi.
Thanks for the great PTO template. When I add/subtract hours using the PTO adjustment tab the overall balance does not change. Do you know what the cause may be?
Fred
You are welcome. Thank you for showing interest in our template.
We regret the inconvenience caused. Can you please share your copy of the file with sample data having the highlighted issue at the below link to check further from our end:
https://support.indzara.com/support/tickets/new
Best wishes.
We have been using your spreadsheet for 5 years and it works great! Our employees start earning 40 additional hours of PTO after year 5. We had not used the Tenure table until now. I inputted all the information and the calculation is not right. I’m not sure what I’m doing wrong or if changing it at this point is the problem.
Thank you for using our template and sharing your valuable feedback.
We regret the inconvenience caused.
Please share your copy of the file with some screenshots and sample data having the highlighted issue at the below link. We will be glad to assist you:
https://support.indzara.com/support/tickets/new
Best wishes.
Thank you so much for creating this template! It has been a great tool for me in tracking my own PTO accrual and usage.
Would it be possible to have an additional option of entering a date added for the PTO Rollover Timing field? My company applies PTO rollover each year on a specific date (July 1st) for all employees, so having the option of entering a date in the PTO Rollover Timing field and having the rollover amount automatically applied as of that date would be incredibly helpful. Thank you!
Thank you for using our template and sharing your valuable feedback.
The requested feature of entering a particular rollover date is available in our premium version of the template. Following is the link to the template for quick reference.
https://indzara.com/product/hr-templates/small-business-paid-time-off-manager/
Best wishes.
Hello. I just purchased the managers template.
I am a little confused on accrural rate for PTO. We give a set number of Vacation Hours to employees based on how many years they have been with us. Then ALL employees are eligible to accrue PTO Time as they work. I don’t know where I need to include these accrural rates so that it will calculate properly based on the amount of time an employee has been with us, how many hours they work in a pay period, etc.
Thank you for purchasing the template.
The requested features is available in PTO Manager Hourly template. I have share the Hour template file to your email ID.
Best wishes.
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.
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.
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.
Thank you for showing interest in our template.
Yes, it is possible. But the free version of the template supports only one employee. You can purchase the premium version of the template and write us back to us at the below link to get the template with rotation week off by adding week off in employee sheet.
https://support.indzara.com/support/tickets/new
Following is the link the template for quick reference:
https://indzara.com/product/hr-templates/small-business-paid-time-off-manager/
Best wishes.
Is this available as a google template? the link won’t open for me, and this is exactly what i’m looking for!
Thank you for showing interest in our template.
Following is the link to the Google Sheet version of the template:
https://docs.google.com/spreadsheets/d/11o8v-1IYax2-sJOHn7estwUd0puomwRrD4NP7jWxbo8/edit?usp=sharing
Please make a copy of the template to your Google Drive to use the template.
Best wishes.
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?
Thank you for showing interest in our template and sharing your valuable feedback.
The requested feature of having custom date is available in our premium template. Following is the link to the PTO manager template:
https://indzara.com/product/small-business-paid-time-off-manager/
Best wishes.
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.
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.
I have about 100 employees, could I have the template for multiple employees? Also, is there a template that tracks both Vacation & Sick separately?
Thank you for showing interest in our template.
We have a premium template of PTO tracker and that is PTO Manager. The same can be purchased from the following link and it solves both your requirement:
https://indzara.com/product/small-business-paid-time-off-manager/
Best wishes.
A hire date prior to 2001 gives an error message in the PTO hours. If I plug in a start date of February 24, 1997, for example, cell P3 is “#N/A ” and I can’t find the formula error.
Our support team will respond to you shortly.
In the meantime, if you require any support on our product, requesting you to send an E-Mail to support@indzara.com
I have been using the PTO Calculator for almost 2 years now. I love it!!! However, I do have a question…
The file is getting rather large now and slower to open due to its size. What is the best way to start a new calendar year file so that the file only contains the current year? How do I set the values on the EMPLOYEE PTO tab for a new year?
Thank you, Katrina
I have 28 employees to track. Can I get this tracker with all 28 in one workbook?
Thank you,
Ronda Haddock
Thanks for your message.
Employee PTO Tracker & Calculator 2020 – Free Excel Template, calculates PTO for a single employee. For more than one employee, please review our premium template, Small Business – Paid Time Off (PTO) Manager – Excel Template at https://indzara.com/product/small-business-paid-time-off-manager/.
Best wishes
Hi there. I downloaded the individual PTO calculator with the idea of trying it out before we go with the full package for our small 50ish employee firm. I’m using my own data as the guinea pig and I cannot get it to calculate properly. It seems the problem stems from the “first accrual date” question. I have watched the YouTube tutorial a few times and looked at the web site and still not getting it. Here is the data i have.
Hire Date 01/09/10
90 day probationary period
Start date through end of first calendar year earns .769231 per week.
First full year earns 40 Hours
2 to 9 years earns 80 Hours
10 to 19 years earns 120 Hours
20 + years earns 160 Hours
No carry overs
When I think I have all of the data entered properly I get a “40 Hours” calculation. Which should be “120 Hours”.
What am I doing wrong. I’d love for someone to look at the worksheet that i filled out and point out my problem. Thanks.
Thanks for using our template.
Can you please share the file along with the list of issues to contact@indzara.com.
Best wishes
this is great, However I am using the “PTO Unit” as “Hours” but the “enter PTO Hours” is actually calculating by 7-hour days up in the “PTO Balance on” cells ( I think)… I need to understand how to change that to 8-hour days if i cannot get it to work using hours.
Hello
If you choose Hours, you need to enter PTO taken by employees in Hours. Please refer to the blog and the video at https://indzara.com/2015/09/employee-pto-tracker-free-excel-template/
Thanks
Hello,
I love this template – thank you so much!
I have an employee with over 20 years of tenure. I can’t figure out how to add more days on the CAL table. I tried copying and pasting, but I receive a Value error. I understand what it is supposed to do, but I am an Excel novice. Will you please tell me how to add more days?
Thanks!
Thanks for your message.
Please do not copy-paste. Instead, you may drag down the table in the CAL tab and get the values. It will take some time to populate, but it will work.
Best wishes
Great Program. I am happy that I purchased it! Just what my pre-school needs!
This template is great! Thank you for creating and sharing. Our PTO accrues every six months (one week every six months). Could you add a field that allows for every six months after hire anniversary? Thank you.
Thanks for your feedback.
Unfortunately, it will take some development to implement the once every 6 months setting.
I will include this in the road-map for future versions.
Best wishes.
We have 16 employees. Thank you!
I just downloaded the excel and it seems great. However, when I adjust the “Annual PTO Accrual Rate” from “120” to “80” the “PTO Balance on XX hours” doesn’t reflected or get updated. I checked the hidden “CAL” tab as well as manually refreshing all and its still not working.
Thanks for using our template.
This issue seems to be a one-off case. Please download the file again and check the same. At times a deleted formula can be the reason. In case it still does not work, please share your file along with the list of issues to contact@indzara.com.
Best wishes
What if the accrual policy is every 30 hours worked, one hour of PTO is accrued.
How can that be calculated?
Hello,
PTO can accrue in hours. However, the working days are calculated in days.
Best wishes
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
Good Afternoon,
I am looking to use this template, however we recently went to PTO January 2019. Thus older employees have a little different amount as we switched from vacation time to PTO. Is there a way to say or add a section for Carry Over, plus accrued equals the final amount?
Thank you for your help and time.
Thanks for your interest.
Any one-time adjustments can be entered in the Adjustments sheet. Please provide more details if there are any further questions.
Best wishes.
Awesome! So lets say… I had before the new PTO, carried over 136 hours. I would place that on the third tab, and continue to place the current information on the Balance Calculator, and the spreadsheet would automatically adjust the end balance?
Another question, when a Data Validation Error occurs, I have edited the window however the error remains?
Any suggestions?
Yes, you are correct. Please email your file to contact@indzara.com and we can review the error you are referring to.
Best wishes.
i need help i need to learn how to do this step by step. from what i was told you calculate by the years you been to the company/agency, not by hired date, is that correct. How do I begin?
Thanks. You can find details on how to use the template in the above post, along with the video demo.
The template calculates accrual rates based on tenure or fixed accrual rate.
Best wishes.
I have been using this for a year now. I really like it. However, lately it is doing something really weird in cell P4 (PTO Balance Hours). I currently have pto hours in cells P34, P35 & P36. So, it is showing 127 hours in cell P4 (I have it set as unlimited rollover). I need to add 80 hours in cell P37 for a vacation I took in Aug 2018. When I insert 80, it changes the amount in cell P4 (PTO Balance) to negative 833 hours. So, I tried adding just 8 hours in cell P37 and it changed the balance (P4) to 31 hours. It’s still wrong.I have no idea what is going on but it is frustrating. Please help. Thanks!
Hello
Please send your file along with the list of issues to contact@indzara.com
Best wishes
The template is awesome! How can I add an additional PTO option? example PTO 1, PTO 2, PTO 3
thanks!
Hello
Do you wish to track PTO for more than one employee? Please use the premium template https://indzara.com/product/small-business-paid-time-off-manager/
Best wishes
Hi –
I am interested in purchasing this template, it looks really good. I have a question, though. Is there a way to not prorate and employees first month of accrual? We allocate our PTO in full days and do not want to deal with partial accruals.
Thanks!
Thank you for the interest.
The template allows adjustments table where we can enter negative values to reduce any pro-rated PTO accrual. I am assuming you are referring to employee not getting any PTO accrual if they start in the middle of an accrual period. If you want to provide the whole accrual amount without pro-rating, then please use the adjustments table to add PTO balance to the employee.
This would have to be done once for each employee who starts in the middle of the accrual period.
Please let us know if there are any questions.
Best wishes.
Is there a way to update the calculation for hours under PT0 balance to account for 1/2 hour increments? Say if someone takes 2 and half hours instead of 3 hours off.
It shows the correct balance on the chart but I’d like to see it reflected in the balance as well.
We can enter partial hours in the PTO hours table. The Balance at the top is set to not show decimals by default. However, we can just increase the decimals in the Number formatting menu.
Then, the balance should show in decimals.
We have also emailed you these details with screenshots.
Best wishes.
Is there a template similar to this for PTO that is front-loaded and then as the year goes along any new hires are given a pro rata percentage that matches the percentage of time left in the year?
Hello
Please review Small Business – Paid Time Off (PTO) Manager – Excel Template at https://indzara.com/product/small-business-paid-time-off-manager.
Best wishes
First of all, amazing work, great job! The structure and usability is awesome and meets almost every need imaginable. I wanted to ask if there is a way to track any PTO plan where it is accrued PER HOUR rather than per pay period. Is there a way to do this with the existing file or is there a formula I can add somewhere that can enable this. I’d greatly appreciate your help. Thank you for providing this for those of us who need it
Hello,
Thank you for your feedback.
Please check “Hours” in the PTO incurred unit.
Best wishes
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
Amazing tool; however, your PTO adjustment table is not “talking” to the CAL table. If I need to add an additional 40 hours to an employee’s balance, I put in the date and type in 40, but this does not adjust the balance. Can you please take a look and determine where the error occurs?
Hello,
Please ensure that you have entered the dates correctly. In case it still does not work, please send your file with the list of issues to contact@indzara.com.
Best wishes
Sorry – ignore the last question – I just figured it out
Thanks for letting me know. Best wishes.
How do you add more rows in the PTO adjustment sheet or for PTO dates in the Employee PTO sheet (Cell N34:P35)
Thank you for this sheet, it has been great. I am trying to use the formula you posted before for Total Used PTO (=SUM(T_PTOTAKEN[PTO Hours])). The result of this formula does not match what has been entered. I even went to the CAL worksheet and summed the numbers there, and they are different than what the formula above returns.
Can you tell me how to calculate the total number of used PTO for the current year? My theory is that it would be something like =sum(index(CAL table,today(year),column M), but I am not good enough to figure it out.
Thank you very much! I wouldn’t ask, but I think this is something others would find very useful as well.
Thanks.
Please provide examples to clarify when formula result does not match data entered.
To find the total PTO used for a specific date range (eg. year), then use SUMIFS function. This allows us to sum the PTO used column where date >= 1/1/2017 and <= 12/31/2017 (or today's date). Best wishes, .
No words to describe! You’re simply the best than all the rest!
Employee PTO Tracker & Calculator – Free Excel Template <== Excellent Tool!!!
Thank you so much for sharing!
You are welcome. Thanks for taking the time to provide feedback. Glad it is useful.
Best wishes.
Hi Indzara,
First of all this template is very well done and organized beautifully!
I purchased the PTO Manager v1_3 and I was wondering if there is a way to have the PTO and sick time calculated based upon the hours worked ( paid Bi-weekly)
We have some employees who are on salary and some who are paid hourly.
The calculations for both are:
Hourly Sick Time 0.034 hrs per hr worked
Hourly PTO Time 0.032 hrs per hr worked
Salary Sick Time 2.72 hrs per pay period
Salary PTO Time 2.56 hrs per pay period
Is there any way you can add an hourly accrual section where I can input their hours worked every two weeks and it’ll calculate based upon that?
Thanks for purchasing.
Sorry, currently the template does not handle accrual based on hours worked. It is something that needs to be built in the future.
Best wishes.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
Hi Indzara,
This template is amazing!
I have a question regarding the “PTO Rollover Timing”…I have my option set to “Calendar Year” however, the annual PTO accrual hours are not being added to the balance on Jan 1st.
For example, my employee was hired on 4/1/16, annual accrual rate is 24 hours per year. Accrual begin date is 6/30/16 (90 day probationary period). Accrues at the beginning of the period, with rollover limit of 48 hours on Jan 1st (calendar year) with a maximum PTO balance of 48 hours.
With the above information put into the “Inputs” the graph shows 24 hours added on June 30th 2016, which is correct, then an additional 24 hours added on June 30th 2017. Shouldn’t the 2nd accrual have been on Jan 1st 2017?
Hey Indzara,
Thank you for providing this template for excel. Our employees accrue PTO based on the number of hours they worked that week. This there a way to calculate this into their spreadsheet? Say they accrue 4 hours per 40 hour week. They only work 30 hours one week, so they should only have 3 hours accrued. Is this possible?
You are welcome. Sorry, that feature is not currently available.
Best wishes.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
Hello,
First, let me tell you how awesome this template is! Very very helpful.
However, I actually accrue PTOs every six months, and that’s not included in the options. I can use the annual option as a temporary workaround for me, but is there any way that this can be edited so that it’ll have that option?
Please let me know. Thanks for this again!
love the product!! what a useful tool! i’m planning to purchase the PTO for multiple employees template. one quick question – is there a way to track when an employee takes a half day of PTO? we award PTO based on days, not hours so I’m not sure how to track half days off. thanks!!
Thank you very much.
Given how the template is set up, we have to use the ‘Hours’ setting if we need to enter partial day PTOs. We have to enter all the data in Hours.
Please let me know if there are any further questions. Best wishes.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
This product is perfect for tracking a single employee’s time! The thought and hours you have put into this product really show. I do have one recommendation, however. On the “CAL” tab could you modify the formula to reset any negative value to zero? Given that values are rounded it is nearly impossible to put in the EXACT number of hours available to use which, if the available hours are rounded up, leads to a negative value if anyone inputs the exact hours available.
Thanks again for all your hard work!
Thanks for the feedback. Can you please clarify what you mean by ‘exact number of hours available’? Thanks.
Thank you.
If I understand correctly, you need only the rolled over portion of the PTO balance to expire in first quarter. I am sorry that this scenario is not directly captured in the template. We consider the entire balance as one entity and not distinguish between rollover over and newly accrued.
We can apply manual adjustments in the Adjustments table to make it work, but it would be more manual data entry.
Best wishes.
Hi, like many others, your template is amazing. We have been using a simpler excel version to track PTO, bereavement and rollover. But, we can only roll over 40 hours total per calendar year, and the rollover must be used by the end of the first quarter or we loose it. Our regular PTO continues to accrue whether we have rollover or not. How would I set this template to zero out rollover by the end of the 1st quarter each year?
Thanks so much!
I would like to have each PTO divided and calculated by the specified category names i.e. sick days, vacation time, personal days, leave of absence in order to have a more accurate numbers of PTOs used. Is it possible to modify this template to include different categories or what would you recommend on how to decipher the accrual and use of vacation time, sick time and floating holidays separately, etc. Wouldn’t it be a whole new template or could we use this same template for these individual categories as well? Thanks for your help.
This template is designed for only 1 PTO Type. The PTO Manager template https://indzara.com/product/small-business-paid-time-off-manager/ can handle two types of PTO, for multiple employees in 1 file.
Best wishes.
It appears that when entering time used the hours default to fixed 4 or 8 hours.is it possible to allow time used to be entered in smaller increments such as 1.5 hours ?
You should be able to enter any number of hours. I will email you. If there are any issues, please reply. Thanks. Best wishes.
This program is very helpful but I do need to be able to use it for up to 75 employees, is that possible?
Thanks for your interest. I have published a new template that can handle multiple employees.
https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks. Best wishes.
Hello, I work for a company that has 4 employees and we have a cap on our time off that we are allowed to receive total and carry over. I am looking at this spreadsheet and see that I can only use it for one employee. Am I able to copy the tab for each employee?
Thanks for your interest.
This template supports only one employee and sheets cannot be copied to add employees (as there are calculations in hidden sheet that support only one employee’s data). You can have a copy of the entire file to manage additional employee’s PTO.
I am currently working on a new template to handle multiple employees in 1 file. I hope to publish it soon this month. Please stay connected to our social media outlets to be notified. Thanks for your interest. Best wishes.
I have published a new template that can handle multiple employees.
https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks. Best wishes.
Thank you for sharing this amazing template with us. Appreciate your effort.
Do you have one for 5-10 employee? Thank you.
You are very welcome.
I am currently working on one to handle multiple employees. I hope to publish it soon this month. Please stay connected to our social media outlets to be notified. Thanks for your interest. Best wishes.
I have published a new template that can handle multiple employees.
https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks. Best wishes.
Do you have a program now for 20+ employees?
Thanks for your interest. I don’t have one for multiple employees yet. I have replied to your email asking for more details on your needs, if you need something developed for your business. Thanks. Best wishes.
I have published a new template that can handle multiple employees.
https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks. Best wishes.
This is quite the spreadsheet! Is there a way to have it calculate for hourly employees or did I completely miss that? For instance, my employees earn 1 hour per 40 hours worked and on bi-weekly pay periods. The problem is they clock in and out so some weeks they work less than 40 hours and don’t earn a full hour of PTO for that week and other times they work more and would earn more than one hour. So I need something to calculate based on that. What would this cost to produce? Thank you!
Thanks for the kind words.
The template that is published is based on earning PTO via tenure. It does not handle earning PTO by work hours. We need to significantly redesign the template. I have emailed you asking for more specifics before estimating the cost. Thanks. Best wishes.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
This spreadsheet is the best i have seen! Is there a way i can get a spreadsheet that is designed to manage 35 employees? Thank you!
Thank you very much.
I have emailed you to get further details on the requirements. Thank you.
Interested in the PTO spreadsheet designed to manage 75 employees. Do you have a model for multiple employees? How much would cost?
Thanks.
I don’t have a file for multiple employees yet. I have emailed you to get further details on the requirements. Thank you.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks & Best wishes.
Hello Indzara,
This spreadsheet is amazing, thank you for putting it together.
I was wondering if there is a way to set the PTO Rollover Timing to a fiscal year, rather than the calendar year or anniversary year? Our fiscal year is Nov 1st-Oct 31st.
Thank you for your time!
Thanks for your kind words. Sorry, I don’t have it setup currently to have the option to change rollover timing to any day. You can edit the hidden sheet where calculation is done and modify the formula accordingly.
I take on projects for a fee as well. If interested, please email me at indzara@gmail.com. Thanks. Best wishes.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/
In this template, the PTO rollover timing can be set to any date.Please review and let me know if there are any questions.
Thanks & Best wishes,
I was wondering if you could possibly assist me with creating a PTO Calculator for vacation and sick days. Here is the information that I have been given. We have 250 employees and are continuing to grow.
Sick time accrues.5 days per month for a maximum of 6 days per year. Sick time does roll over.
Vacation time accrues as follows: Does not roll over
1-36 months= .5 days per month
37-120 months=.833 days per month
121-360 months= 1.25 days per month
90 day probationary period for every employee. Neither may be used prior to end of 90-day probationary period, however, both start accumulating on date of hire.
Please help me if you can. I am at loss of how to set this up myself and really need help.
Thank you so much!
I am very sorry for the delayed response.
The template doesn’t handle two types of time off.
Also, the tier based PTO accrual is based on yearly and not by months (36 months in your case).
Also, PTO does not accrue during the probationary period.
It would require significant changes to the template to make it meet your needs. If interested, please email me at indzara@gmail.com I can provide an estimate of development. Thank you.
Thanks for your feedback. I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
In this template,
2 types of PTO (example: Vacation, Sick) can be managed.
PTO accrual can vary by tenure in months.
We can add manual adjustments to PTO balance at the end of probationary period.
Please review and let me know if there are any questions.
Thanks & Best wishes,
Hi,
I need the ability to create a single workbook for multiple employees. My company offers sick time pay. other than this two things, this is wonderful. Thank you!
Thank you. Can you please clarify how many employees should be supported and what are the policy rules around sick time? Thanks.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/
In this template, 2 types of PTO can be managed. Multiple employees can be managed as well.
Please review and let me know if there are any questions.
Thanks & Best wishes,
This is amazing! However, as do so many others, I also need the ability to create a single workbook for multiple employees. What I have not seen in the comments is a request to see, within the same sheet, the ability to track a secondary type of PTO. Most companies that offer vacation also offer sick pay and that is what would top this template off for me. 🙂 Other than those two things, this truly is a fantastic template. Thank you!
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/
In this template, 2 types of PTO can be managed. Multiple employees can be managed as well.
Please review and let me know if there are any questions. Thanks & Best wishes
I think this template will be perfect for our company. We have around 50 employees right now. It would be great to have access to a file that would let me track all of the employees. Thanks.
I agree! This template is great, however I’d also like to see one that could support approximately 50 employees. Thank you!
Thanks for the feedback.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/ where PTO for multiple employees can be managed.
Please review and let me know if there are any questions.
Thanks & Best wishes.
Thanks for the feedback.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/ where PTO for multiple employees can be managed.
Please review and let me know if there are any questions.
Thanks & Best wishes.
Thank you for the template, really looking forward to the multiple employee template.
You are welcome. Please let me know how many employees need to be supported. There could be some challenges with file size if the number if very high. Thank you. Best wishes.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/ where PTO for multiple employees can be managed.
Please review and let me know if there are any questions. Thanks & Best wishes.
This template is AMAZING!!! Thank you so very much for creating this!
You are welcome. Thanks for the feedback. I am glad that it is useful. Best wishes.
Thank you!! Yes, we pay our employees every other friday, so PTO is only accrued on payday fridays.
0-3 months 0 hrs accrued
3mo-12mo 56 hrs accrued
1yr – 5yr 104 hrs accrued
6yr – 15yrs 144 hrs accrued
16+ yrs 184 hrs accrued
We start PTO accrual period on Jan 1st of every year (it starts over each year) with a max rollover of 40hrs. We have a lot of turn over as well in our staffing so I am having to track each employee’s PTO starting on a different date. For example:
Employee X hire date is 06/01/16 so they don’t start accruing PTO until the pay period after 09/01/16. They won’t get the full 56hrs accrued since they started mid year. I love the spreadsheet but I can’t figure out how to get the accrual start date to adjust…the formula in the “PTO balance hrs” doesn’t seem to begin on the correct start date that I enter. Thanks so much for your help!!
I am working on the next version where your requirements should be met. It is taking longer than expected due to the complexity with many different scenarios requested by different users. I am in the final round of testing now. Thanks.
Thanks for the detailed response. The template has been upgraded now to allow ‘Twice a Month’ accrual. Please download and let me know your feedback. Thanks.
I have published a new template https://indzara.com/product/small-business-paid-time-off-manager/ where PTO for multiple employees can be managed.
PTO Accrual rates can vary by tenure (in months).
Probationary period can be set where employee does not accrue PTO.
If someone becomes eligible for PTO in the middle of an accrual period, prorating will be done automatically.
Please review and let me know if there are any questions.
Thanks & Best wishes.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
I love this tool! Question… We pay bi-weekly.. there is no set date for our payroll, it’s literally every other Friday.. how can I change the tool to show every 2 weeks instead of the 14th of each month?
Thank you!
Thank you.
Regardless of the employee’s hire date, is it always on every other Friday? this is somewhat unique and I need to build some logic to enable this. There are so many variations used by different business. 🙂 Please share your company’s business rules for PTO policy. I am working on this template this week. Thanks.
The company I work for is the same way. We get paid every other Friday except we accrue the PTO every other Saturday. Perhaps a drop down to enter an example accrual date? Then the sheet can calculate every 7 or 14 days depending on the accrual rate for the employee.
I am working on the next version where every 2 weeks would be an option and the user can set the accrual period begin date. I am in the final round of testing now. Thanks.
Perhaps also an option where you can put your current balance as of today. The current format forces the user to put previous PTO dates already taken for the year.
I am working on the next version where starting balance can be added by the user. It is taking longer than expected due to the complexity with many different scenarios requested by different users. I am in the final round of testing now. Thanks.
The template has been upgraded now to allow ‘Twice a Month’ accrual. Adjustments can be made to PTO balances easily now. Please download and let me know your feedback. Thanks.
Sorry if this posts twice,
Do you have a template that does have the ability to have multiple employees?
Thank you!
Not a problem. I don’t have one yet. But I am prioritizing this now and hope to have one within 2 weeks. I am trying to understand from users what accrual frequencies are commonly needed and other settings. Please share your business’ PTO policy rules either here or to my email indzara@gmail.com. It will be helpful to build the correct options. Thank you.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks & Best wishes,
this is incredible, I love it thank you! we have a total of 160 employees how would I use this for my company?
Thank you very much.
I am working on upgrading the options available in this single-employee template. Once I complete that, I will create a multi-employee version. Please email me (indzara@gmail.com) your business’ PTO policy rules. It will help me build a product that handles common needs. Thanks.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks & Best wishes,
I would also like to see bi-monthly. My dates are the 5th and the 20th. How would I set it up to have those dates as the PTO accrual dates? (6.67 for each date).
Thanks!
I am working on upgrading this template. I will include the option to choose dates (twice a month). I hope to publish later this week or next week. Thank you.
The template has been upgraded now to allow ‘Twice a Month’ accrual. Please download and let me know your feedback. Thanks.
Indzara,
Love this template and it works so smooth. Will be looking out for the template where I can manage PTO for multiple employees at the same time.
General Question,
Having a earning method “At the beginning of each year” and “Remaining hour does not carry forward”.
Having a pay period from Dec 27 to Jan 4, and pay date on Dec 31. How the hours should be accrued? Whether the accrual based on pay period or pay date?
If the accrual based on pay date, Employee cannot claim the PTO for Jan 1 to Jan 4 since the pay date lies on previous year. Similarly, If the accrual based on pay period even then the same case prevails. how should I handle this..??
Thank you.
1. Earning method “At beginning of each year” – please choose ‘Granted’ for Accrual frequency and ‘Calendar Year’ for Renewal Timing
2. “Remaining Hour does not carry forward” – please choose ‘No Rollover’ as Roll over policy.
In the above scenario, Annual PTO (let’s say 80 hours) will be given on the 1st of each calendar year. If the employee does not take any PTO, the balance will be 80 until Dec 31st. On the 1st of next calendar year, it will be reset to 80 hours again (these are 80 new hours after applying 0 rollover). Please let me know if there are any questions. Thanks.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks & Best wishes,
Indzara,
This is an amazing tool, and it is great as is. I have run into one problem though.
The “max allowed PTO” does not work well with the tiered system implementation, because in many cases, an employee’s “max PTO” changes in year X. So, for example, in years 0-3, accrual = 120 hours/y, rollover = unlimited up to the max PTO of 120 hours but in year 3-4, the max PTO = 160 hours.
The current implementation does not allow for a different max PTO depending on the tiered year. If a future version of the Excel had that, along with semi-monthly frequency, it would be *perfect.*
Thank you so much for doing this work!
Thank you for the feedback. I will do my best to improve it in the next version. Best wishes.
The template has been upgraded now to allow varying Maximum PTO by tenure. Please download and let me know your feedback. Thanks.
I plan on implementing this template for my company, but would LOVE LOVE LOVE to see it be able to handle multiple employees in one workbook rather than 30 different workbooks. Thank you for this so far though.
Thank you. Great to hear that it is useful. I will do my best to create one that will handle multiple employees. Best wishes.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions.
Thanks & Best wishes,
Sure. Have you tried entering 160 hours as annual PTO accrual and accrual frequency as Monthly? Is it not working as expected? Please let me know.
Another question: If an employee gets 20 day off per year (or 160 hrs), then they accrue at a rate of 13.34 hours per month or 6.67 hrs semi-monthly. Currently the template cannot track portions of hours. How would you account for this?
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
I would love to see changes for semi-monthly accrual! Thank you!
Oops, repeat comment. Thanks for all your help!
Not a problem. 🙂
Hello Indzara, I love this template. Is there a way to add lines to the completed tenure? Thank you!
Thank you. Yes, you should be able to add lines to completed tenure. Please try and let me know if it doesn’t work. Best wishes.
Thank you for this! This is awesome!
Is there a way to calculate the usage so that instead of taking the whole 8 hours it only take the hours that were used? since my company manages by hours.
Thank you!!!!
The file allows entering enter PTO taken in hours each day. Please let me know if that doesn’t address your question. Thanks & Best wishes.
In moving to this template we will have a one-time rollover of PTO hours that we need to add in somehow. Did you already build a simple process into your spreadsheet to include this?
The spreadsheet is built with new employees in mind or employees for whom the PTO history is available. I fully agree with you that having the ability to provide starting PTO balance will be a good addition to the next version. Thanks & Best wishes.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
In this template, we can enter one-time starting balance for each employee. Thanks for your feedback.
Please review and let me know if there are any questions. Thanks & Best wishes.
Hi, this template is great! Thank you. Like Rachel, I need to accrue based on semi-monthly pay periods. I have zero idea how to edit. Please advise. Regards
Thank you. Does ‘Semi-Monthly’ here means 2 times a month? If so, is it always on 1st and 15th? Please clarify.
It appears that Rachel has already worked on this option. Hopefully, she can share that with you. I am tied up with other projects and am sorry that I couldn’t add this option myself. I hope to, soon. Thanks. Best wishes
I am interested in Semi-Monthly as well. The 1st and 15th of the month is correct (at least in my case). I have tried to understand the mechanics of the spreadsheet so I can add it myself but I am rather confused. Is there a summary file that explains how the Help sheet works that we could look at?
Or can you post in the comments what the additional code would need to be (I think only Help: Column D would need adjusting other than the Data Validation which is simple) and I/we can just paste it in and copy down? It should be a variation of “Monthly” if I am not mistaken that essentially just adds an extra day.
Thanks for the feedback. I have not had a chance to work on this. I will post an upgraded version when I am able to get to it. I am sorry for not having a specific timeline for this yet. Best wishes.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
This allows ‘Twice a Month’ accrual policy as well.
Please review and let me know if there are any questions. Thanks & Best wishes,
I would also love to see this directions to modify for Semi-monthly accrual. Thank you! This has been unbelievably helpful!
Thanks, Anneka. I will do my best to add this feature soon. Best wishes.
The you have a chance to work on this, can you also explain how to adjust to make accrual occur on the 15th and last day of the month? Thank you!
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
This allows ‘Twice a Month’ accrual policy as well.
Please review and let me know if there are any questions. Thanks & Best wishes,
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
In this template, we can enter ‘Twice a Month’ pay periods easily.
Please review and let me know if there are any questions. Thanks & Best wishes.
Hello, Thank you for this template, it is very useful. My company was doing PTO based bi-weekly frequency (per pay check). We have just switched to Semi-monthly. How do I add the semi-monthly frequency to the to column B7? Also, I know I can go to the “Help” tab and modify columns D and E to account for the switch. Thank you.
You are very welcome. Please click on cell B7. Then, choose from the DATA ribbon –> ‘Data Validation’ –> ‘Data Validation’. This will open up the dialog box which lists the values in the drop down. You can add ‘Semi-Monthly’ there. If you have already modified the file for semi-monthly, please feel free to share with Patty (comment below). Thanks & Best wishes.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
In this template, we can enter ‘Every 2 weeks’ & ‘Twice a Month’ pay periods easily.
Please review and let me know if there are any questions. Thanks & Best wishes.
I have formatted my spreadsheet to show the PTO Accrual in hours. I work with medical providers who sometimes work extra time on days that they would not ordinarily be in the office in exchange for additional vacation time. I thought I had it figured out to add negative hours into the time off request (in hours) column but sometimes it works in the total PTO accrual and sometimes it doesn’t. For instance, we do not allow carryover but I had one provider start this year with 101 extra vacation hours from working extra time. I tried to add an entry for the date at the start of the accrual period and put in for (101.00) hours. Although it shows up and is formatted as a negative value, it does not add to the accrual. Any suggestions?
Thanks for the comment.
Please unhide the hidden sheet where calculations are done. Please check for the specific employee how the PTO balance hours are calculated. I am currently tied up with other projects and am unable to look into it immediately. Thanks for understanding.
Best wishes,
It would also be nice if the Enter PTO Dates column had a total used at the bottom
Please type the following in cell F12 =SUM(T_PTOTAKEN[PTO Hours])
This will calculate the total PTO hours. Thanks & Best wishes.
I am implementing your template form my team. We are set up as days off. Is it possible, when entering days, to put in the amount of days instead of having an entry for every day?
2 examples
1. I have someone who needs a .5 day off and the form does not allow for that
2. If an employe takes 10 days off it would be nice to enter the date it starts and then the number of days they are taking off.
Right now your “Enter PTO Dates” only allows for hours. Is there a way that if you select the Annual PTO Accrual column C to “Days” that it would then turn the Entered PTO Dates F column to days off. Then you could enter the number of vacation days used for that time frame.
Thank you for the feedback.
For this work with ‘days’ instead of ‘hours’, several formulas have to be modified. Similarly, to accommodate a range of days, formulas have to be modified. Unfortunately, I am tied up with other projects now. I will definitely consider your feedback for the next version when I get a chance to work on this. Thanks & Best wishes.
Hello,
Great sheet. My only issue working with it is that my company awards 2.5 days of PTO per 3 month period (not monthly or weekly). Any way to modify the table?
Best,
David
Thank you. Sorry for the delay. Multiple changes are needed to implement it. Accrual Frequency of ‘Quarterly’ needs to be added first in the drop down in Employees sheet. Then in the hidden sheet ‘Help’, we have to update formulas in columns D and E to accoun for this new frequency. Please let me know if this helps. Thanks & Best wishes.
hello is there a version that allows me to enter the actual accrual amount earned? I earn 4.67 per month after the initial 90 day probationary period so right now the form isn’t calculating quite accurately the true number of hours earned. The max is 42 but if you divide by 12 I only get 3.5 so it makes more sense to provide the actual amount earned. Please let me know. I otherwise love the template and very much hope to use it until my company moves into the 21st century. Thanks!!
Thanks. I am glad to know that the template is useful.
The current version does not handle probationary period in months. Also, the accrual rate is set annual and not monthly. I am sorry about that. I will consider this when I get to upgrading this template in the future.
Best wishes.
Is there a way to use this with employees that have over 10 years of employment? If I enter a date more than 10 years in the past it will not formulate the PTO balance.
The current template is designed for up to 10 years by default. There is a hidden sheet with calculations where you can extend them to more than 10 years. I plan to release a version in the future with more features including longer duration. Thanks & Best wishes.
This is wonderful! I may be missing it, but is there a way to have it stop accruing PTO until an employee uses time off and the PTO total drops below a certain limit? We earn hours based on tenure and accrued per pay period. However, once a year’s worth of PTO has been accrued, no more is added until 40 hours of PTO have been used. Thank you!
Thank you. The template has a max PTO limit – where the balance can never go beyond. Does that address your question? Thanks.
Hello
We have designed a template that calculates PTO based on the hours worked. Please review the template at https://indzara.com/2019/11/pto-balance-calculator-for-hourly-employee/
Best wishes
I would also like to use this for a small company pto tracker for 8 people. Is there any way to copy/paste within the same workbook?
I am sorry. In its current form, copying and pasting within the same workbook will not work. I need to re-design to accommodate multiple employees in one workbook. I will look into the effort it will require and post back here. Thanks & Best wishes.
We are a small company with 10-15 employees. I attempted to use your template for each employee by copying and pasting the template to several sheets within the workbook and renaming each sheet by the employee name. The problem is that the tables, accruals, dates, etc. within the graph would all reference the original sheet. Is there a way to fix this or would I have to download 10-15 copies of the template and save separate workbooks for each employee?
Thanks for your interest. The template is designed for only one employee at a time. You are correct. To use for multiple employees, you would have to use copies of workbooks. I plan to publish a version that can handle multiple employees if there is enough interest from users. Thanks & Best wishes.
Do you have a version with multiple employees available?
Thank you!
I don’t have one yet, but it looks like there is enough demand for it now from readers. I will prioritize it . I will update a tentative publish date shortly. Thanks.
I have published a new template that can handle PTO for multiple employees. https://indzara.com/product/small-business-paid-time-off-manager/
Please review and let me know if there are any questions. Thanks. Best wishes.
This is great, thank you. In our company, the employee does not accrue time until after their 90 probationary period. Can that be added? Also, in the template, when I set the Hire Date as 1/15/2015 with the Annual and Max accruals set to 10, the PTO balance as of 12/15/2015 shows 9 with no PTO Dates listed. Seems like it should be 10. Am I doing something wrong? Thank you!!
You are welcome. Please note that the template currently allows tenure based PTO accrual only by Years. In your case, you are looking for 90 days. If it was in years, you can use the Tenure based tiers table provided.
Regarding your example, if the hire date is Jan 15, 2015, by Dec 15, 2015, it is not one whole year and hence the balance is 9 instead of 10. Please check the PTO accrual frequency. If you choose ‘Granted’ (meaning the employee gets all 10 days on day one), then the balance will be 10. Please let me know if there are any questions. Thanks.
Also, if an employee try to use more PTOs that given, is it possible to give them alert by any appropriate way? For example, it can show negative numbers in red or something like that.
Please advise.
Yes, please add a conditional formatting rule $K$11 < 0 and choose red background fill. This will make the current PTO balance cell K11 become red when the current PTO balance is less than 0. Thanks.
Our employees earn PTO days when they work during weekends. So is there any way that I we can add PTO days that will be made in irregularly basis?
So for example, I punch in my additional working date and it automatically add my PTO days.
Please advise.
I am sorry. I am not sure what ‘earning PTO during weekends’ means. The template currently is based on PTO being earned by calendar time (all at once, weekly, bi-weekly, monthly). I am guessing you mean they may get additional PTO when they work during weekends. Am I correct? Please clarify with example so that I can follow correctly. Thank you.
I am also interested in being able to define the amount of hours used per PTO event, rather than having the PTO date subtract 8 Hours. Can you post it or send it to me as well?
Nice work!
Thank you. I have emailed you the file. I will post it here when I get a chance. Best wishes.
indzara i like this format and I’m going to integrate this with our training team that has 13 trainers. is it possible to have a drop down list to change from trainer to trainer? also, we also have compensation days, days we can take that are not PTO. example: travel on a saturday but it was for work, so i’d take another work day as compensation. we also get sick time as well. if you’d prefer, you can respond to my work email, which i provided and design a template so I can manage the PTO for this team. in return I’m willing to donate to you via paypal.
Thank you. I have e-mailed you my response. Best wishes.
Hello,
I am interested in this feature. I played with the template and it works beautifully with a sample employee. I am curious how you add employees. I tried to copy the template into a new tab for a second employee but some functionalities get lost and the graph section does not get updated with the second employee’s PTO dates.Thanks!
Thank you. I don’t have this feature built out yet. This template only supports one employee in one workbook. If you copy the entire workbook as a new one, then you can manage the second employee’s PTO. I will be publishing a new template soon that will allow managing a team of employees. How many employees do you need to manage? It will be helpful for my product. Thanks. Best wishes.
This tool is excellent!
Can you also send me a copy one that I can use for multiple employees? About 20 people.
Thank you
Thank you. I don’t have a version for multiple employees yet. I will publish here once it is available. Please subscribe to our social media channels to be notified. Thanks.
Hi is there a formula i can change the usage that way instead of taking the whole 8 hours it only take the hours that were used? since my company manages by hours
I have emailed you a version where PTO can be entered in hours. Please let me know if that helps.
If others are interested in that version, I can post that here and replace this one. Thanks.
Really appreciate the PTO tracker!
I just have one request here, we are allowed to only carry over a max amount from year to year.
If i end my PTO year with 40 hours of PTO – it rolls over to my next year, but it doesn’t double, it caps.
It just stays at 40 until I use it, until my next tenure year arrives. Then I begin to accrue from 40 up to my next tenure amount.
Having the PTO double like that is a liability to us in a sense.
Is this something you could develop by chance ?
Thank you. I am glad that you find the template useful. Rollover Policy and max rollover can be set in the template. (PTO ROLLOVER POLICY) Does that not meet your expected result? Please let me know.
Best wishes.
This is perfect! I also am having the same request. I can only rollover a certain amount of hours per year and can only accrue a max amount. Right now, I have my PTO balance as more than I’m allowed to have max. How can I edit this spreadsheet so that it doesn’t let me accrue any more than the max amount of hours?
Thank you. I am glad that you find the template helpful. Rollover Policy and max rollover can be set in the template. (PTO ROLLOVER POLICY) Does that not meet your expected result? Please let me know.
Best wishes.
Thanks for the response. I see the PTO ROLLOVER POLICY and I’ve set it to 240 hours. The only issue is that for my current PTO BALANCE, it’s showing as 245 hours, but I’m not allowed to go over 240. I hope that makes sense.
I see your point now. The rollover policy limit applies only to the renewal date (at the end of the renewal period), but during a period there is no limit. I will look into it and see if i can change it. I am currently working on some other projects but I will get to it as soon as I can. Thanks. Best wishes.
I have updated the post above with the latest version of the file. Added features:
Enter PTO taken in hours each day.
Set a max PTO that can not be exceeded anytime.
Hope this helps. Thanks.