Our Small Business PTO Manager Excel template can be used to calculate and report PTO balances for employees in a company. One of the features of the template is the Calendar which shows the monthly view of PTO taken/scheduled by the employees. By default, this sheet was limited to show 40 employees at a time so that it can fit within 1 page to print. If your company has more than 40 employees to track PTO, then this could be a limiting factor. In this blog post, we will learn how to extend the Calendar view to show more than 40 employees.
The default calendar view looks like this.
It would stop with 40th Employee.
For this exercise, let’s assume we have 80 employees in our company. We will use the following simple steps to view more than 40 employees.
Option #1 (40 employees at a time)
If you don’t need to see data for all 80 employees in the calendar at the same time, then we have a very easy solution already built-in.
Just type 41 in cell A7.
Now, you will be able to see data for employees 41 to 80.
Option #2 (More than 40 employees at a time):
This involves a few more steps.
Before we begin, please save a copy of the file for backup. We will be making formula changes and it is always recommended to create a backup.
Tip: It is also recommended to create backups regularly even if you don’t make formula changes.
Step 1: Unprotect sheet
Please unprotect sheet with password indzara
Step 2: Select Row 45
Click on 45 to select row 45.
Step 3: Right click and choose ‘Copy’
Step 4: Select rows 46 to 86
Select rows 46 to 86 (we need to select 1 row more than our desired expansion. Since we want to expand by 40 rows, we select 41 rows)
Step 5: Right click and choose ‘Insert Copied Cells’
Step 6: Select row 87
Click on 87 to select row 87
Step 7: Clear Row 87
Right click and choose ‘Clear Contents’
Step 8: Change Print settings
Since the calendar will be more than 1 page long, we need to make some modifications to the Print Settings.
First, Click on ‘Print Titles’ in the Page Layout ribbon.
Set rows 1 to 6 to repeat. This allows the 6 rows to print on second page (employees 41 to 80) as well – allowing the reader to align the dates to the calendar.
Step 9: Protect sheet
Now we are done with making the changes to extend the calendar. We can protect the sheet again to prevent unintentional editing of formulas.
Support Page: For all the help articles on Small Business PTO Manager Excel template
Product Page: For product features and highlights of the PTO Manager Excel Template
It is designed such that we can continue to use the same file for many years. By default, the tracking period is set to 2 years. This was set so that the file size is smaller, and the calculations are faster. When we extend the tracking period, Excel needs to do a lot more calculations and store a lot more data. So, we limit the default period to 2 years, but provide an easy option for the user to extend the period in few simple steps. In this blog post, we will see how we can easily extend that to more than 2 years.
If you prefer video demos, please watch the video.
Before we start the process, it is important that we understand the Start Date concept used in the template first.
You can choose to start tracking PTO in any year from any date. By default, the template has 1-Jan-2018 as starting date.
You can change this Start Date to any date.
The template will then assume 2 things:
1) PTO balances of employees as of (31-Dec-2017) the date prior to Start Date will be entered in the Employees sheet.
2) All PTO for employees from 1-Jan-2018 will be entered in the Time Off sheet
This is how we ensure that you can use the template to track PTO in companies where you have employees who have been working already. They have already accrued balances and you can just enter them as starting balances and then use just this template to track new PTOs from now onwards.
Tracking Period Limit
In some cases, you may have detailed PTO dates for each employee tracked for the past few years and you may want to migrate that detailed data to this template. Or if you have purchased the template in 2017, you would reach the end of default tracking period of 2 years in 2019.
For example, we want to track PTOs from 2010. The Start Date would be 1-Jan-2010.
The PTO Report though will not show current balances as of today (March 2018 when this tutorial was written).
And the Balance TrendChart will only track until 2 years from Start Date.
(To illustrate, I have entered 4000 as the number of days to display on the chart.)
Now that we understand the limitation, let’s start our process to extend the tracking period.
6-Step Method to increase tracking period
Overview of steps
Unhide hidden CAL sheet
Find the end of table and extend calculations down
Check PTO Report sheet for verification
Hide the CAL sheet
Save changes in File
Step 1: Backup
We recommend saving copies (archiving) of your files regularly as there is no other backup system.
Similarly, before making any extensions like we are going to do now, please save a copy of your current file separately with a different name (for example PTO_Manager_Archived_2018_03_18.xlsx) and then use your main file for the following steps.
Step 2: Unhide the hidden CAL sheet
Right-click on a sheet name and then choose Unhide.
Choose CAL and then click OK
Step 3: Extend calculations
Find the end of the calculations table. Click anywhere outside the table. The last cell of the table will appear as shown below.
Hover over the right bottom corner of that cell. When the cursor becomes double edged arrow, click and drag down.
We dragged down until row 3653. This extends the calculations until 12/31/2019 (10 years from 1/1/2010).
Step 4: Verify
If you now go to the PTO Report sheet, you will see current balances.
The Chart will now track until 12/31/2019.
Step 5: Hide the CAL sheet
Now that we have completed extending the period, we need to hide the sheet.
Right-click on CAL sheet name and choose Hide.
Step 6: Save the changes
Until we save the file, all the changes made so far will not be retained.
Press Ctrl+S or use the File menu to save the file.
Now, we have extended the tracking period to 10 years from the default 2 years.
In the PTO Manager template, we can set the PTO accrual to be based on a tenure table.
For example, employees start with 12 days of annual PTO accrual, and once they complete 24 months of employment (tenure), then they start earning 15 days of PTO every year. We implement this using the tenure table where the user can enter their choice of tenure months, PTO accrual and Max PTO, according to their company’s PTO/Leave policy.
By default, the table has 3 rows and the sheet is locked. In some companies, there could be more than 3 tenure tiers. Let’s see how we can add more rows to this table in the simple 4 step process.
Why we lock the spreadsheets
In some of our templates, we protect (or lock) the sheets using a password. This is done to prevent the user from accidentally editing/removing the formulas used. We also give the password to the customer so that they can unprotect and edit if they are familiar with Excel and formulas.
Auto-Expanding Excel Tables
If you are completely new to Excel tables, please visit our Introduction to Excel tables. Excel tables have a default feature by which if we enter data in the row immediately following the last row of the table, it will auto-expand and include the new row as part of the table. This is one of the breakthrough features that almost all the templates from indzara.com depend on. There is a catch though. This feature does not work if the sheet is protected.
Step 1: Unprotect or unlock the sheet
Navigate to the Review ribbon and click on Unprotect Sheet.
A dialog box pops up where you can enter the password indzara and click OK.
Step 2: Enter data to add to the table
Now that the sheet is unprotected, we can add rows to the table.
To add one row, type the data in the row immediately following the last row of the table. Here, we will type the number in cell B40 (last row of the table is 39) and press Enter key.
After pressing the Enter key, the table will expand to include row 40. You can confirm this by seeing the border line now move to row 40.
To add one more row we can enter data in row 41 and press Enter key. As simple as that.
Step 3: Protect the sheet
Once we have entered as many rows as possible, we can protect the sheet again to prevent accidental modification of formulas.
In the Review ribbon, choose Protect Sheet option.
Enter a password and then click OK. There is no need to change any of the other options.
Now you have protected the sheet again.
Step 4: Save the changes
After protecting the sheet, please save the changes to the file. Otherwise, the changes you made will not be saved.
Press Ctrl+S or use the File Menu.
If there are any questions about this article, please post them in the Comments below.
In this article, we will go through the steps involved in using the PTO Manager Excel template. To learn more about the features of the template, please visit the Product page.
This article is part of a series of support articles for PTO Manager Excel Template. For more support, please visit the Support Page.
Overview of Steps
Enter Weekends and Holidays in the Home sheet
Enter policy inputs in the PTO Policy Settings sheet. Review first accrual window.
Enter one employee’s data in Employees sheet. Review the PTO Report sheet.
Enter list of all employees in Employees sheet
When employee takes PTO, enter PTO info in Time Off sheet
View PTO balance and balance trends in PTO Report sheet
View PTO days on calendar view in Calendar sheet
(Optional): To apply adjustments, enter in PTO adjustments sheet
If you prefer to watch video tutorial, please see the demo video
Step 1: Enter Weekends and Company Holidays
To begin with, we will enter weekends in Home sheet.
Here, I have selected Saturdays and Sundays as Weekend days. This will be used by the template to count every Saturday and Sunday as non-working day. If an employee takes paid time off (PTO) that includes a Saturday and Sunday, those 2 days will not be counted against the PTO balance.
Company holidays are given the same treatment. We can enter all our company holidays in the table.
Weekends and Holidays will be the same for all employees in your organization.
We are done with the Home sheet. Let’s move to the PTO Policy Settings sheet.
Step 2: Enter PTO Policy Settings
Though the template is simple to use, there is a lot of flexibility when it comes to designing the PTO policy.
There are some terms to understand and since different companies may have different interpretation for those terms, it is important to state clearly each one of them. I will explain all the possible options in each setting so that you can pick the one that applies to your company.
Let’s say you begin using this template on 1st Nov, 2016. You want to track PTO from 1st Nov in this template.
If you have employees who have already been working in your company and have accumulated a PTO balance, then you would need their balances as of 31st Oct, 2016. We will enter this balance data in a later step.
The general settings apply to both types of PTO (template supports 2). The following inputs are provided.
We can choose to track employee PTO in units of Days or Hours. If we choose Hours, we must 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.
If you choose ‘Days’, please note that partial days (example: half day) are not allowed as PTO. When you choose Hours, you can enter any partial number of hours as PTO.
PTO ACCRUAL PERIOD
This is to inform how we accrue (at what frequency) the annual PTO rate. For example, a company may grant their PTO weekly. Every week, certain hours of PTO will be added to the PTO balance of the employee.
This template supports the following accrual frequencies: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.
We will see each of these in more detail. But before that, we need to introduce a couple of other terms.
FIRST ACCRUAL PERIOD BEGIN DAY
Continuing with the example scenario of weekly accrual, let’s say the accrual happens every Monday in your company.
In the image above, I have shown 6 accrual days (Mondays) from Oct 31st to Dec 5th.
Since Nov 1st (when we started using the template) is a Tuesday, the active accrual period then is from Oct 31st to Nov 6th. So, our First Accrual Period Begin Date is Oct 31st, 2016.
We enter this First Accrual Period Begin Date in the settings.
If the First Accrual Period Begin Date is after the Start date, then an error message appears.
Please ensure that the First Accrual Period Begin Date is not after the Start Date.
Since we accrue at the beginning of the accrual period, we choose that setting as well. In some companies, accrual happens at the end of the period.
Review First Accrual Period for accuracy
In the PTO Policy settings sheet, you will be able to instantly view the first accrual period calculated by the template.
It is very important that you review this and make sure that it meets your business’ expectations.
A check mark appears where the accrual happens. Here, in this example, accrual happens at the beginning of a period.
If the accrual timing is End of the period, then a check mark will appear next to 6th Nov (End) and 6th Nov will be the accrual day.
We can also see the number of accrual periods per year.
It is 52 for Weekly, 26 for Every 2 weeks, 24 for Twice a Month, 12 for Monthly, 4 for Quarterly and 1 for Annual.
The data entry in ‘General Settings’ is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. So, let’s look at ‘Twice a month’.
Twice a Month Accrual Period
For ‘Twice a Month’, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days instead.
The template will then take those two days as the accrual days every month. In the example above, accrual will happen 1st and 15th of every Month.
First accrual period will be 1st Nov to 14th Nov. Number of accrual periods per year will be 24.
You can also 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.
The First accrual window will change accordingly.
Monthly Accrual Period
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.
I have chosen the 1st of every month here.
The first accrual period will be Nov 1st to Nov 30th and the accrual will happen on 1st Nov.
This completes the steps in setting the accrual days and periods.
Now, we need to inform the template the amount of PTO accruals and other inputs.
Entering PTO details
The template supports 2 types of PTO to be tracked. For example, Vacation and Sick days are common in companies. Both can be tracked in this one file.
For the upcoming illustrations, let’s assume the following accrual period inputs in General Settings.
PTO unit is ‘Days’. Accrual happens on 1st of every Month.
We can choose how many types of PTO we need to track.
Let’ start with just selecting 1 type of PTO.
We can then enter more details about that PTO type.
We can give a name. I have entered ‘Vacation’. You can customize it as needed.
ANNUAL PTO ACCRUAL RATE
Annual Accrual Rate is the PTO that an employee accrues in one year. In our example, the company offers 12 days of PTO per year.
12 days will be given to the employee annually at 1 day each month for 12 months.
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.
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, a very high balance could mean more liability 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.
Let’s see them one by one. I will explain the impact of each option and this should help you pick the one you prefer for your company. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
Choose Zero Rollover option in the drop down.
To understand the impact of this setting, see chart below.
At the end of 1 year of employment (Nov 2017), the employee’s PTO balance comes from 12 days to 1 day. The employee loses all 12 days of PTO due to Zero rollover policy.
Then, employee accrues 1 day for Nov 2017. So, the balance becomes 1 day.
Rollover Limit: We can set a limit on how many hours are carried over.
I have chosen a 4 day roll over limit in this example.
The impact can be visually seen below.
At the end of 1 year of employment (Nov 2017), the employee’s PTO balance comes from 12 days to 5 days. It is 5 because 4 days are carried over and the employee accrues 1 day for Nov 2017.
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.
At the end of 1 year of employment (Nov 2017), the employee’s PTO balance increases from 12 days to 13 days. The employee carries over all the 12 days and accrues 1 day for Nov 2017.
Hence, the balance becomes 13 days.
You might have noticed that I talked about this rollover happening after ‘completion of 1 year’. That is because we had chosen Work Anniversary as the PTO Rollover Timing. This setting is also customizable per your business policies.
PTO ROLLOVER TIMING
Companies may apply rollover at calendar year change (Jan 1st of every year) or on work anniversary dates (varying for each employee based on hire date). Some companies may also align this with their financial calendars. To support that, we have the ‘Custom Date’ option. Let’s learn more about this setting.
Since we have already seen how Work Anniversary option works, let’s see how Calendar Year option impacts the balances. I am using Zero Rollover policy for this illustration.
The PTO balances are shown below.
When it is Jan 1, 2017, the balance becomes 1 day. Balance from 2016 cannot be carried over and Jan 1, 2017 brings a new 1 day of PTO accrual. Then, the employee accrues 1 day each month and reaches 12 on Dec 1, 2017. Then, on Jan 1, 2018, balance is set to 1, exactly as it was done on Jan 1, 2017.
Let’s review the last option in PTO Rollover timing: Custom Date.
We have chosen Apr 1, 2017 as the rollover timing. This is to inform the template that Apr 1st of every year, PTO rollover policy should be implemented.
The PTO balances are shown below.
When it is Apr 1, 2017, the balance becomes 1 day. Balance from Mar 31, 2016 cannot be carried over and Apr 1, 2017 brings a new 1 day of PTO accrual. Then, the employee accrues 1 day each month and reaches 12 on Mar 1, 2018. Then, on Apr 1, 2018, balance is set to 1, exactly as it was done on Apr 1, 2017.
PROBATIONARY PERIOD & MAX PTO BALANCE
Let’s cover next 2 settings together.
In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee may not earn any PTO during the first 30 days of employment. You can set that easily in this template as shown above in the Probationary Period.
MAXIMUM ALLOWED PTO BALANCE
The rollover limit (that we have discussed earlier) only applies to the balance as of specific timing (calendar year/work anniversary/custom date). Some companies can set a limit on maximum PTO balance an employee can carry at any time. We can set that amount in the Maximum Allowed PTO Balance.
We can see the impact of these settings on the balance chart as shown below.
Now, we move to the final part of the PTO Policy settings.
ACCRUAL RATES VARY BY TENURE
Companies increase the annual accrual rate for employees who stay with the company for more years. This is a loyalty initiative which rewards loyal employees with better PTO benefits. We can handle such scenarios as well.
We would choose YES to this first.
Once we choose ‘Yes’, the accrual rate and max PTO balance mentioned in the above image does not apply. Instead we must enter that information in 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 12 days annually in the first 24 months, then rate of 15 days annually in months 25 to 36 and rate of 18 days annually after 36 months of tenure.
Important: Please make sure that the first entry here is for 0 completed months.
If the work anniversary happens to be in the middle of an accrual window, then we have to pro-rate the PTO accrued.
Let’s take an example where an employee’s hire date is Jan 16th, 2016. Accrues 10 hrs a month in 1st year and then 20 hrs a month in 2nd year. So, for Jan 1, 2017, employee will earn 15.16 hrs. 15 days (from Jan 1st to Jan 15th) at the rate of 10 hrs per month and 16 days (Jan 16th to Jan 31st) at the rate of 20 hrs a month. The template does this prorating calculation by default.
Managing Two Types of PTO
This completes the policy settings for our Vacation PTO. If we need to manage two types of PTO for our company, then, we need to follow the following additional steps.
First, enter the number of Paid time off as 2.
Then, enter details of second PTO Type.
Just like we entered for PTO Type 1, give this a name. I have given here, ‘Sick’ as name.
The settings are the same as we discussed earlier. The only thing we need to be aware of is that Rollover timing and Probationary period are automatically set to match with PTO Type 1’s. Other settings can be different between PTO Type 1 and PTO Type 2.
We are now set up to track two types of PTO. Green line represents Vacation days balance and Blue line the Sick days balance.
Step 3: Enter a sample employee data and review PTO Report
Enter one employee’s data in Employees sheet so that we can review the calculations and ensure that it works as expected.
Let’s enter one employee name and hire date. In this case, hire date >= Start Date (Nov 1st). Hence I don’t need to provide any starting balances. Starting balance carried over is zero.
In the PTO Report sheet, choose Employee name from the drop down.
To refresh all the calculations, press Shift+F9. If the keyboard shortcut doesn’t work in your computer (sometimes keyboard shortcuts are taken over by other applications), then go to Formulas ribbon and press Calculate Sheet.
We can see the employee’s details as well as current accrual rate for the employee.
Further below in the report, we can see the accrual days.
For 2016, we see only 1 accrual day. Dec 1st. This is because the employee has 30 days probationary period and will be eligible to accrue only from Dec 1st.
If we change the year to 2017, we will see the accrual days for 2017.
1st of every month will be shown in light blue font indicating the accrual days.
This step should confirm that the accrual days are correct and meeting your business expectations. We should also check the amount of PTO accruing. For that we see the balance chart in page 2 of the report.
Green line represents Vacation days Balance and Blue line Sick days Balance.
You can see that the sick day balance drops down on Nov 1, 2017 since we have Zero Rollover policy and the balance for Vacation days continue to grow, as we have Unlimited Rollover policy for that PTO type.
Verify whether the numbers match your expectations. Once you are comfortable with the calculation results, we are ready to enter all the employees’ data. If the numbers do not match your expectations, please contact me (email link is provided in the Home sheet).
Please note that once the settings are entered once, they don’t have to be modified again. Once you start calculating balances using this template, please do not modify the settings later. Any change in settings will impact the Employee PTO balances.
Step 4: Enter All Employees’ data
I have entered 10 employees’ data for the demo here.
If an employee has left the company, please enter termination date.
For employees who had balances as of Start Date (when you began using the template), then enter starting balances.
If you were manually tracking PTO balances for employees, you can just take the final balance for each employee and enter in the table. This is a one-time step to migrate from your previous system to this template.
For example, employee 6 who was hired in Mar 2014, has a balance of 4 vacation days and 2 sick days.
Step 5: Enter Paid time off information
The next step is to enter Paid time off information when employees plan to take time off.
Enter PTO taken by employees in the Time Off sheet.
Employee Name, PTO Start Date, PTO End Date and Time Off Type are required fields.
If an employee takes only one day off, please enter the same date in PTO Start Date and PTO End Date columns.
If the PTO unit was chosen as Hours, then PTO Hours column should be entered. An example is shown below.
Daily PTO hours: Please enter PTO hours taken for each day even if you are taking multiple days together as PTO.
Step 6: View Employee PTO Report
We can view the employee’s PTO balance at any time by viewing the PTO Report sheet.
User input cells are filled in yellow color for easier identification.
We can choose an employee and see the report.
To refresh all the calculations, press Shift+F9. If the keyboard shortcut doesn’t work in your computer (sometimes keyboard shortcuts are taken over by other applications), then go to Formulas ribbon and press Calculate Sheet.
The accrual days are shown in the calendar in light blue font. Vacation days are shown in black fill and Sick days in red fill.
We can also see the monthly totals of PTO earned and PTO used by the employee.
The second page of the report has the starting balances information.
If the employee started earning PTO during the middle of an accrual period, then prorating will be done and will be displayed.
PRORATING WHEN EMPLOYEE STARTS IN MIDDLE OF ACCRUAL PERIOD
We all know that employees can start in a new job on any day. Just to illustrate the prorating, let us take a slightly different example. An employee was hired on 15th Nov. Probationary Period set to 0 days.
We can see that the first effective accrual window is still 1st Nov to 30th Nov, and the accrual happens on 15th Nov.
However, the amount is only 0.533 days of vacation because the employee only accrues for 16 days (15th Nov to 30th Nov) out of the 30 days in the month. So, 16/30 = 0.533. So, the employee accrues 0.533 days instead of 1 day. From the next month onwards, the employee will accrue 1 day per month of vacation. This prorating is only for the first period.
Thus the template can easily pro-rate the PTO awarded when an employee joins in the middle of an accrual window.
We can also see the trend chart which shows PTO balances and PTO used over time.
The duration of this chart timeline can be customized as needed.
Step 7: View calendar
The Calendar sheet is provided to help get a view of the entire team’s PTO for a month.
We can change the Year and Month to view the calendar for that month.
The two PTO types (Vacation and Sick days) are shown in Black and Red fill colors. Holidays are shown in Purple and Weekends in W.
If the employee is not employed on a specific day, then NE is displayed.
You can change the symbols used by just typing in the appropriate cells in the legend. For example, If I replace V with P, then the calendar also will reflect that immediately.
The sheet also shows the totals for the month for each employee.
This information could be used for resource planning for a month, as you have a good view of number of days each employee is expecting to work in the month.
Thanks for visiting the support page for PTO Manager Excel Template. The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.