The Small Business PTO Manager allows tracking PTO accruals and balances for employees.

For more about the template – Product Page

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.

Start Date – Date of tracking PTO 

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.

Starting Balances for Employees 

2) All PTO for employees from 1-Jan-2018 will be entered in the Time Off sheet 

Enter PTO for employees from Start Date 

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).


PTO Report not showing Balances 


And the Balance Trend Chart 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.)

Report Chart shows only 2 years 

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

  1. Backup file
  2. Unhide hidden CAL sheet
  3. Find the end of table and extend calculations down
  4. Check PTO Report sheet for verification
  5. Hide the CAL sheet
  6. 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.

Right Click on a sheet name and then choose Unhide 


Choose CAL and then click OK 

Unhide CAL sheet 


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.

End of CAL table 

Hover over the right bottom corner of that cell. When the cursor becomes double edged arrow, click and drag down. 

Hover, Click and Drag Down 

We dragged down until row 3653. This extends the calculations until 12/31/2019 (10 years from 1/1/2010).

Extended calculations for 10 years 

Step 4: Verify

If you now go to the PTO Report sheet, you will see current balances.

PTO Report showing Balances 

The Chart will now track until 12/31/2019. 


Balance Trend Chart for 10 years 

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.

Hide the CAL sheet 

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.