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