How to increase tracking period in PTO Manager Excel Template

Posted on

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.

 

VIDEO DEMO

 

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
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
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
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
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
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
Right Click on a sheet name and then choose Unhide

 

Choose CAL and then click OK

Unhide CAL sheet
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
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
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
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
PTO Report showing Balances

 

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

Balance Trend Chart for 10 years
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
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.


Recommended 

Leave a Reply

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