If you are looking for a simple and effective Timesheet template or Time Card template, you have reached the right place. In this blog post, I will present a free time sheet template in Excel and explain step by step how we can use it to calculate hours worked.
You can enter time card entries like time in, time out and break time and let the template create printable weekly timesheet, biweekly timesheet and monthly timesheets.
If you are an employee, no more wondering ‘How many hours did I work?’ or searching for ‘How to calculate hours worked?’. This timesheet template makes it easy by automating all the calculations.
If you are an HR (Human Resources) professional who needs a work hours calculator for the employees in the company or team, you can use this too. The template accounts for various commonly used rules in the industry and makes your task simple and easy.
Essentially, this template can be used by anyone who needs a timesheet in Excel or work hours tracker or payroll hours calculator.
In this blog post, we will see step by step how we can use the Resource Capacity Planner Excel template. We will take a simple example planning scenario and see how the template can help us in smarter planning.
The Resource Capacity Planner template is designed to determine the available resource capacity, compare with demand and identify surplus/deficit in capacity. It also allows us to modify the resource availability or demand (to meet our objectives) and see impact of the changes instantly.
For more on template’s features, please visit the product page.
Template Version
This user guide is for the latest version v3 of the template. If you are using a previous version and need the new file, please email support@indzara.com with your Order Number. If you need support with the previous version, please email your questions to support@indzara.com
Overview of steps:
Enter Settings
Enter Resource list with standard availability and allocate Capacity to Skills and Projects
Enter vacation and overtime
Enter Demand (tasks) data
Refresh Calculations
View Dashboard to evaluate plan
Address any over-utilization and under-utilization
View Calendar to ensure desired utilization at granular time periods
Step 1: Enter Settings
In the Settings sheet, we will enter the basic settings.
Planning Period
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.
Step 1 Enter Planning Period Dates
We will use a 3 month period from Apr 1, 2019 to June 30, 2019.
Weekends
In most work places, we have days during a week where we don’t work (also called weekends). We can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.
If you don’t have any weekends, then leave this input blank. Just click the cell and press the Delete key. It will remove the contents of the cell. Please do not delete the column or row.
Enter Weekends
We will choose SAT & SUN as weekends for this tutorial.
Enter Settings – Weekends
If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.
Excel Tables
In the next set of inputs, we use Excel Tables. Entering the data correctly inside the Excel tables is critical to the functioning of the template. If you are new to Excel Tables, please see our article on How to use Excel Tables for data entry?
Tip: Start entering data right below the Header of each Table
Holidays
Since most companies have holidays (other than weekends), it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.
We will enter a couple of days in the Holidays table.
Step 3 – Enter List of Holidays
If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.
Projects
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.
Enter Settings – Project List
Skill Groups
Skill Group is how we can group resources for planning.
Skill Group and Skill are used interchangeably in the template.
In our example, we have resources from 2 skill groups: Project Mgmt and Development.
Enter Settings – Skill Group List
Resources
Enter list of Resources available for planning.
The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used.
Let’s assume we have 6 resources in total.
Step 6 – Enter list of Resources
Step 2: Enter Resources’ Capacity
Now, we move to the Capacity sheet in the template.
We must enter the resources, provide start and end dates of availability, available hours every weekday and other information related to capacity.
Tip: To help with data entry, you can copy the values you entered in the Settings sheet, for Resources, and then paste in the Capacity sheet. Please make sure that you paste as values.
After copying the values, right click on cell A4 in Capacity Sheet. In the menu that pops up, choose Values under Paste Options. This ensures that only the values are carried over, without impacting the formatting and drop-down validations.
Pasting Resource Names as Values
After entering resource names, we enter the Start Date of each resource.
Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.
Enter Resources Capacity – Available Work hours by Weekday
We enter the number of hours each resource will be available each weekday. For example, Project Planner 1 is available 8 hours a day from Mon to Fri. The template can handle varying hours by weekday as well.
We assign the 6 resources each to a Skill Group and Project.
We can provide the End Date of each resource if there is an End Date for the availability. If no end date is provided, the template will assume that the resource is available through the end of the planning period.
Resources Capacity Table – Entered
Finally, we enter the Cost per Hour for each resource.
Important: The last column is a calculated field that is critical to the functioning of the template. Please do not edit/delete it. If you don’t want to see it, you can hide the column
How to allocate same resource to multiple skills?
You can allocate each resource to one or more skills.
To allocate one resource’s capacity to multiple skills, add a new row to the table and assign a different skill. Ensure the available work hours are distributed between the two skills.
For example, if Developer 4 was available 6 hours in total and can perform Development role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.
Entering same resource with multiple skills
Resource name will be same Developer 4, but work hours may vary, and Skill Group will be different. In this case, we assigned to same Project B.
How to allocate same resource to multiple Projects?
You can allocate each resource to one or more Projects.
To allocate one resource’s capacity to multiple projects, add a new row to the table and assign a different Project. Ensure the available work hours are distributed between the two Projects.
For example, if Developer 4 was available 6 hours in total (Development skill), and will work 4 hours for Project B and 2 hours for Project A, we would enter as shown below.
Entering same resource with multiple Projects
Resource name will be same Developer 4, but work hours may vary, and Project will be different.
How to allocate same resource to multiple Projects and Skills?
You can allocate same resource to one skill in one project and a different skill in another project.
For example, if Developer 4 was available 6 hours in total, and will work 4 hours in a Development role for Project B and Project Mgmt role for 2 hours for Project A, we would enter as shown below.
Entering same resource with multiple Projects and Skills
How to shift same resource to a different Project after a certain date?
If we want a resource to work in one project for a certain date range and then change to a different Project, we can use the same approach. We can use the END DATE column to enter the end date of a certain Project assignment and then enter a new row with a different project.
Assigning a resource to different projects over time
In the above illustration, Developer 4 works on Project B for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project A.
You can apply the same method if you want a resource to change skill from a certain date.
Let’s go with the simple setup now where each resource is assigned to only one skill and one project.
Limitation:
Now, the above examples would have made it clear that this Capacity table can have more than 1 record per resource. Though the default Resource limit is 100, this table can accept 300 entries.
For example, if you have 50 resources, each resource assigned to 2 skills on average and 3 projects on average, that would require 50 X 2 X 3 = 300 entries.
300 unique Resource-Skill-Project combinations can be entered.
Step 3: Enter Vacation and Overtime
Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime.
We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.
Enter Vacation Overtime taken by Resources
Project Planner 1 is taking 3 days off (8 hrs each day) from 16th Apr to 18th Apr.
Two important things to note here:
It is 8 hrs of vacation each and hence totally 24 hours in total for all 3 days. However, we only enter 8 hrs as the column is for daily hours. The template will calculate automatically the total.
If the employee’s vacation hours vary over the three days, then we must create separate rows.
We enter -8 since it is vacation. If it is overtime, we will enter 8.
If vacation hours for a day is greater than the standard availability of that resource, then the result will be set to 0 to avoid negative availability.
Project Planner 2 is taking one day off (8 hrs) on 21st May. We enter 21st May as Start and End Dates. It is important that we fill both dates.
Finally Project Planner 2 also does overtime on 25th May. We enter 8 (not -8) in Hrs, since this is overtime.
This 25th May is a Saturday and is a weekend day. However, overtime can be done on weekends and holidays as well. The template supports that.
Overtime is positive to capacity and Vacation is negative to capacity.
This completes our capacity inputs. Now, we move to the Demand data input.
Important:
If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).
Step 4: Enter Demand data
In this template, demand is entered as Tasks in the Demand sheet.
Enter Demand as Tasks assigned to Resource Project and Skill.
Required fields: Date of the task, Resource assigned to do that task, Hours of work required, Project assigned and Skill Group.
Detail column is optional to enter any details about the task or any notes.
The three calculated cells on the right should not be edited or modified. That would break the functionality of the template. Please do not delete or edit these columns.
Do Not Edit Calculated Fields
Planning granularity
An important concept to understand is planning granularity. We have entered daily level of tasks in the above image. What level of tasks to be entered depends on the planning granularity you need.
When we say ‘weekly planning’ it means that our goal is to ensure all the tasks for the week are completed by end of week. If we are behind on Monday, Tuesday or Wednesday, that is okay. As long as we complete by end of week, we will be accomplishing our goals. In this scenario, you don’t have to enter daily level tasks. You could enter just one record for a resource for each week.
If you want to ensure you will be meeting your demand every single day, then, you should enter daily tasks individually.
Thus, the template can accommodate more granular demand entry (daily – even multiple tasks per day) or aggregated demand entry (weekly or monthly or quarterly).
Once we complete entering the demand, we are done with data entry.
Step 5: Refresh Calculations
The template uses pivot tables and hence every time we change input data, please refresh calculations.
Data ribbon –> Refresh All.
Refresh All calculations
Step 6: Review Dashboard
Dashboard
Dashboard sheet shows the summary of metrics across all projects, skills and resources.
Capacity Planning Dashboard
On the left side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).
Dashboard Slicers
At the top, you will see the overall summary of metrics.
Capacity Planning Dashboard – Summary Metrics
You can see the number of Projects (2), Skills (2) and Resources (6) in the Plan.
On the left side, you can see the Capacity (2472 hrs), Demand (2287 hrs), Surplus of 185 hrs at 93% Utilization Rate.
On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,554 while the demand tasks will cost $23,273 resulting in a surplus of $2,281 at 91% utilization.
We also see that there are 2 over utilized resources and 3 under-utilized resources.
Why is this important?
The overall aggregate utilization rate will not provide the complete picture. If there is a Developer resource who is over utilized (utilization rate > 100% or capacity < demand) has 50 hrs over utilized and another Project Mgmt resource who is under-utilized (utilization rate < 100% or capacity > demand) with 50 unutilized hrs, they cancel each other when we aggregate. Though the overall utilization rate may be 100%, individual resources have surplus and deficit and that must be addressed.
Hence it is important to view utilization at resource level.
Changing Currency
To change the currency from US$ to another currency, please follow the steps below:
Press Ctrl+G and select CURR named range.
Changing Currency – Press Ctrl+G and select CURR
Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.
Press Ctrl+1 and Change Currency
Now, the currency on the dashboard display will change accordingly.
Let’s continue with the rest of the dashboard.
Project level Capacity vs Demand
The above visual presents the capacity vs demand for each of the Projects.
On the left, you will see projects where there is deficit – that is, capacity is less than demand. In this case, we don’t have any.
A project will either be in deficit or surplus – not both.
On the right, you will see projects where there is surplus – capacity is greater than demand. Project A has capacity of 1236 hours while demand is only 1097 hours. The surplus is 139 hours.
Project B has capacity of 1236 hours while demand is only 1190 hours. The surplus is 46 hours.
Skill level Capacity vs Demand
The above visual presents the Skills which are over utilized (on the left side) and under-utilized (on the right side).
Development has a total capacity of 1488 hours but demand of 1495 hours. 7 hours overutilized (deficit).
Project Mgmt has a total capacity of 984 hours but demand of 792 hours. 192 hours under-utilized (surplus).
A Skill Group will be in Surplus or Deficit or neither; never in both.
Resource table shows which specific resources are over utilized and under-utilized.
Resource level Capacity vs Demand
Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them. Meanwhile, 3 other resources have a lot of additional hours available but not utilized.
In the next page of the dashboard, you can see the charts of Capacity vs Demand in Hours.
The charts show the overall capacity vs demand for each Project, Skill group and Resource.
The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed.
3 resources have unutilized hours that will be costing the team. In this tutorial, I am assuming the under-utilization is not a major concern. In some project scenarios, underutilization can also be an issue. You can follow similar steps as explained below to address that too.
Now, let’s see how the template will help us address this.
Step 7: Addressing Over-utilization and Under-Utilization
In order to address the over-utilization and under-utilization, we must do one or more of the following:
Change Capacity
Adjust standard availability of resources in the Resources sheet.
Increase (to increase capacity) or Decrease (to reduce capacity) available hours
Add overtime (to increase capacity) or vacation time (to reduce capacity)
Change Demand
Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
Change Allocation/Assignment
Change assignment of tasks (demand) to a different resource.
Change capacity allocation of resource to Projects.
The first option Change Capacity is straight forward and don’t need much explanation. The impact is that it will cost more to increase capacity.
The second option Change Demand is also straight forward to implement in the template (update the demand sheet), though it may be hard to get project stakeholders to reduce the demand (or scope).
We will focus on the 3rd option which may neither increase the cost nor reduce the demand. It is the re-assignment of capacity and demand (tasks) to existing resources to remove over-utilization.
Changing Assignment of Tasks to a Different Resource
Let’s filter the dashboard to Project A by using the slicers on the left of the Dashboard.
The Resources table shows the following.
Assigning Resources in Project A – Capacity Planning
Developer 2 is overutilized by 5 hours while Developer 1 has 37 unutilized hours.
In the Demand sheet, Filter to Resource Developer 2. Find a task for 5 Hours and re-assign to Developer 1.
Reassigning a task to Developer 1
Let’s refresh the calculations. Data ribbon –> Refresh All.
That will update the dashboard calculations.
Project A – Resource – Capacity vs Demand – After assignment
Now, there are no overutilized resources in Project A.
Let’s clear the Project filter in Dashboard.
Now, this is what the Resources table shows.
All projects – Resource – Capacity vs Demand
Developer 3 has 39 overutilized hours, but we don’t have a Developer with that many unutilized hours.
Project Planner 2 who also works on Project B has 85 unutilized hours. To demonstrate how this template can handle a resource having multiple skills, we will assume that Project Planner 2 can handle Development tasks.
Now, we need to assign some of the capacity of the Project Planner 2 to Development skill.
Assign Project Planner 2 Development capacity
Important things to note here:
A new record must be created in the Capacity Sheet as shown above (last row). Resource Name will be same, but Skill will be Development. Cost can vary and can be entered any value.
When we enter the standard work hours of the new record (we have entered 1 hour a day), we must review the work hours already assigned for the resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours for Project Mgmt and assigned 1 hour to the Development. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
In this case, Project Planner 2 has vacation and overtime entries. We create additional entries for the new skill and update the previously entered entries.
Review Vacation and Overtime entries when splitting resources
Now that we have created the capacity allocation for Development skill, we need to assign tasks from Developer 3 to Project Planner 2 in Demand Sheet. Select enough tasks to switch to overcome the overutilization.
Assign tasks from Developer 3 to Project Planner 2
Let’s refresh the Dashboard again.
All projects – Resource – Capacity vs Demand – Final
We have no resources over utilized. 🙂
Overall metrics look like below.
Capacity Planning Dashboard – Summary Metrics – After Optimization
All the resources will have enough capacity to meet the demand. We have 185 hours surplus capacity and are at 93% utilization.
This was just an illustration of how to use the template to modify capacity and demand. The target utilization rate can vary in each organization.
The Dashboard provides the metrics for the entire planning period duration in aggregate. If, in your organization, it is important to ensure a certain utilization every day, then you can update the planning period to a day or use the Calendar sheet to view capacity/demand at a daily level.
Step 8: View Calendar for granular time periods
Calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard.
The below shows the capacity in hours for each resource every week.
Calendar – Capacity by Resource – Hours – Weekly
There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.
Resource Capacity Planner Calendar Settings
Dimension
The calendar can be created for the list of resources or list of skill groups or list of projects.
Calendar Settings – Dimension Resource Skill or Project
Measure
You can select to display Capacity data or Demand data or Surplus/Deficit data.
Calendar Settings – Measure Capacity Demand or Surplus Deficit
Unit
The options for Unit are Hours and Cost.
Calendar Settings – Unit Hours or Cost
Time Dimension
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.
Calendar Settings – Time Dimension Daily Weekly Monthly Quarterly or Annual
The template will display up to 53 periods (53 days, 53 weeks, 24 months, 8 quarters, 2 years).
When you make the selections, the calendar display will update automatically to reflect the selections.
For example, let’s see Surplus/Deficit Hours by week.
Calendar – Surplus Deficit by Resource – Hours – Weekly
We can see that though overall there may be no deficit for a resource based on total hours, some weeks have deficit which are made up by surplus capacity in some other weeks.
If we want to ensure that the team completes the tasks every week, then we need to follow the steps outlined earlier on modifying the capacity or demand.
If there are any questions about this template, please see the Product Support page for more information.
In this blog post, we will cover the most frequently asked question about Recruitment Manager Excel template.
To learn more about the template’s features, please visit the product page.
To learn how to use the template, please visit the support page.
Before we begin, we are assuming that the template’s formulas have not been edited/modified.
Why does the Dashboard not reflect data entered?
This is by far the most common question.
The answer is somewhat long, as we need to cover various scenarios which could lead to this. Let’s explain the different reasons and how to address each of them.
7 Reasons for Why Dashboard does not reflect data
VIDEO DEMO
1. Data Not Refreshed
The template uses pivot tables and hence we should refresh the calculations whenever we make any changes to existing data or add new data.
If you have entered data for the first time in Jobs and Applications sheets, but see that the Dashboard is completely blank, this could be one of the reasons.
Solution: Refresh the calculations. In the DATA ribbon press Refresh All button.
Data Ribbon — Refresh All
2. Errors in applications Data
Applications which have data entry errors will not be included in Dashboard and hence if you have any errors, that could be the reason why you don’t see what you expect in the Dashboard. We put these data validations in place so that the metrics calculated in the Dashboard are always correct.
To identify if this is true in your case, please see ERROR column (column Q in v2 of the template) in Applications sheet.
Error column in Applications sheet
This is a calculated column. So, please don’t edit it. But you can filter using this column to see the applications having Errors.
There are two main reasons why there would be an Error.
Required Dates are missing.
Dates are not chronological
Let’s use a sample data set to illustrate this clearly.
For Hired Applications, all the dates until the hiring stage should have a date. In other words, applications should go through and complete all the stages to be hired.
Errors in Hired Applications
In the above screenshot, first application is missing OFFER DT. The application status is ‘HIRED’. Hence we need to have all the stage dates until the Hire stage.
The second application has all the dates, however, the MGR INTERVIEW DT is Feb 22nd whereas the previous stage PHONE SCREEN DT is Feb 24th. That is an error, as it is assumed that the stages are done in sequence.
Note: Job Posted Date is coming from the Jobs sheet for the specific Job ID. You can also see the Job Posted Date in column Y in Applications sheet.
The third application in the screenshot does not have an error, as the dates are present and they are chronological.
It is a little different for not hired applications. This includes applications with status ‘NOT HIRED’ and applications with no status (where we have not taken a decision yet).
The dates must be in chronological order. However, we don’t need all the dates to be populated. If an application goes to Stage 3 and then is put in NOT HIRED status, then we don’t need dates for Stages 4 to 6.
Errors in Not Hired Applications
In the screenshot above, the first application is missing PHONE SCREEN DT. As it has an APPLICATION DT and MGR INTERVIEW DT, it should also have the PHONE SCREEN DT in-between.
Second application has a MGR INTERVIEW DT (25th Apr) that is prior to the PHONE SCREEN DT (4th May). That is an error.
What if a candidate skips a stage?
Sometimes a candidate may skip a stage and go to the next stage directly. In such cases, it is recommended that we enter the same date and not leave it blank.
For example, if a candidate skips Phone Screen and directly goes to the Mgr Interview because it is an internal candidate and a Phone Screen may be considered unnecessary. In that case, I would enter the Application Date again as the Phone Screen Date.
This will result in the calculations of time taken for Phone screening as 0 days.
Solution: Enter missing dates and ensure they are chronological
3. Required fields are missing
In the Jobs sheet, required fields are Job ID, Job Posted Date, Positions and Status.
Job ID required in Jobs sheet
Required Fields in Jobs sheet
Each Job has to be in one of the three status values (OPEN, COMPLETED or CANCELLED).
In the Applications sheet, JOB ID and APPLICATION DT are required fields.
Required Fields in Applications sheet
Required fields are marked as shown above in the template for your identification.
Solution: Enter any missing values in required fields.
4. Dates in invalid format
Please check that the dates are in correct date format. In certain language and region versions of Excel, the date formats are different from the U.S. version. This could cause Excel to not recognize the entries as dates and thus result in errors in ERROR column.
To check if the date is a valid date format, please click on the cell with the specific value. Then, click on the Number format drop down list.
If Excel shows all the formats with the same value, it means that Excel is treating it as text and not date.
Correct Date format check – Example Invalid Date
If Excel treats as date, it would appear as shown below.
Correct Date format check
Solution: Enter dates in correct format.
5. Filters applied in Dashboard
If you have chosen any filters (slicers) in the Dashboard sheet, that will narrow down the data set used to calculate metrics on Dashboard. If you believe that the Dashboard is not reflecting all the jobs and applications, please check if there are any filters applied. If so, clear the filters.
Clear Filters in Dashboard
Solution: Clear filters applied.
6. Open positions with 0 applications
Template uses data in Applications sheet to build dashboard. Jobs data is also pulled over to Applications sheet.
If we have 0 applications for a job, then they will not included in the Open Positions metric. It will be shown separately next to it as ‘Open Positions w/o Applications in Market’
Open Positions with 0 Applications
Solution: Check if there are applications in Applications sheet for the job. Check the Open Positions w/o Applications metric.
7. No Hired/Relevant Applications
Some parts of dashboard may not be populated if there are no relevant data yet. Each section of the Dashboard calculates certain metric that needs relevant data to make that metric meaningful. If that relevant data is not present, the section will appear blank.The following shows the data that is relevant to each section on the dashboard.
4 KPIs related to Hired Positions – only if there are HIRED applications.
HR Dashboard Summary Metrics
3 KPIs related to Open Positions – even if there are no HIRED applications.
Open Positions with 0 Applications
Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
Recruitment Funnel (up to 6 stages)
Monthly Metrics – only if there are HIRED applications.
Monthly Metrics (Hired and Days to Hire)
Pipeline Efficiency of Hiring – only if there are HIRED applications.
Pipeline Efficiency – Time Spent in each stage
Application Sources – only if there are HIRED applications.
Application Sources – Hired & Conversion Rate
Decline Reasons – only if there are NOT HIRED applications.
Decline Reasons for Candidates/Applications
Cost – if cost is entered in Cost column in Jobs sheet.
Active Pipeline – if there are applications with no status – basically applications where decisions have not been taken.
Active Recruiting Pipeline – Stage breakdown
Solution: Ensure that there is relevant data in the Jobs and Applications sheet.
If you followed the above suggestions and still have any questions, please post them below in the comments section. Thanks for your support.
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.
PTO Manager – Calendar View – Default
It would stop with 40th Employee.
Calendar shows Employees 1 to 40
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.
VIDEO DEMO
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.
PTO Manager Calendar – Enter different starting number
Now, you will be able to see data for employees 41 to 80.
Calendar shows 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
Unprotect Sheet
Step 2: Select Row 45
Click on 45 to select row 45.
Click on 45 to select row 45
Step 3: Right click and choose ‘Copy’
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’
Right click and choose Insert Copied Cells
Step 6: Select row 87
Click on 87 to select row 87
Click on 87 to select Row 87
Step 7: Clear Row 87
Right click and choose ‘Clear Contents’
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.
Select Print Titles
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.
Select rows 1 to 6 to repeat in the Page Setup
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.
Protect sheet
Recommended
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.
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
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 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
Backup file
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.
In the PTO Manager template, we can set the PTO accrual to be based on a tenure table.
Tenure Table in PTO Manager Excel Template
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.
VIDEO DEMO
Step 1: Unprotect or unlock the sheet
Navigate to the Review ribbon and click on Unprotect Sheet.
Step 1 Unprotect the sheet
A dialog box pops up where you can enter the password indzara and click OK.
Enter password to unprotect
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.
Enter data 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.
Table expanded with Border
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.
Protect the sheet
Enter a password and then click OK. There is no need to change any of the other options.
Enter password to protect
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.
Save changes
If there are any questions about this article, please post them in the Comments below.
This article will present step by step instructions on how to use the Team Vacation Planner Excel template.
Purpose
The template is designed to help calculate number of employees available on any day and compare with number of employees needed on that day in the company. This will help us identify if there are any days where we are understaffed. By planning ahead of time, taking into account holidays and vacations coming up, we can be better prepared.
Overview of Steps
Before using the template, it is recommended that you are familiar with Excel tables. I have put together a brief article and video about how to use Excel tables for data entry. Please read this if you are new to Excel tables.
Assuming you know how to enter data in Excel tables, let’s move forward to the overview of steps.
Enter Planning period
Enter Number of Employees needed per weekday
Enter Company holidays
Enter List of Employees and their information
Enter dates of Vacation planned by the employees
View Calendar to see if there are any understaffed days
View Report to see summary of understaffed days by month.
Detailed Step by Step Instructions
Step 1: Choose Planning Period
The template allows planning for up to 366 days. We can choose any start and end date, that accounts to less than 366 days.
Enter Planning Period – Enter Start Date and End Date
In the example above, we have entered Jan 1st to Oct 15th as Planning period.
Step 2: Set Number of Employees needed by Weekday
In some companies, the number of employees needed at work can be the same every day. However, in other companies, there may be a varying need based on the day of the week.
For example, in a company that provides Customer Service 7 days a week, more customers may be calling in during Saturday/Sunday compared to other weekdays. In such a scenario, we need more employees at work during Saturday/Sunday.
This template allows you to customize to handle such scenarios that easily. For each weekday, we can set the number of employees needed.
Set Number of Employees needed per weekday
If we enter 0 as Employees Needed, then the template assumes that as weekend day. In the above image, Saturday and Sunday will be considered as Weekends (for reporting purpose).
During weekends, employees will not be available to work.
Step 3: Enter list of company holidays
Next, we enter a list of holidays in our company.
Enter list of Holidays in company
Holidays are days when company does not operate. Hence, Employees Needed will be automatically set to 0. Similarly, employees will also be set as unavailable.
We can enter any number of days as holidays.
Step 4: Enter list of Employees & their information
We enter the information about employees in the Employees table.
Enter list of Employees, their employment dates and work schedule availability
We enter the name of employee, hire date and termination date (if the employee has left the company).
As this template is designed to be used for long planning periods, we know that new employees may join and current employees may leave midway. Also, if you have contract employees, these situations are more likely. Instead of using a new file to change available employees, you can just update the hire date and termination date where applicable, in the same file. The template will use this information and calculate available capacity automatically.
We can also handle varying work schedules of employees. For example, Employee 2 and Employee 4 do not work on Saturdays , but other employees do. We can just enter No for any weekday an employee doesn’t work.
Step 5: Enter Dates of Vacation planned by Employees
In the Vacations sheet, we enter the vacation planned by employees.
Enter Vacation dates of Employees – Vacation Start and End Dates
This is very simple. Enter Employee name, Vacation Start and Vacation End dates.
If an employee takes only one day off, please enter same date as Start and End dates. Please do not leave the End date empty.
As the employees takes more vacation, just add new rows to this table.
There is no limit to how many vacations can be entered.
This completes our data entry steps.
Step 6: View Calendar to see availability by day
The Calendar sheet is fully automated.
At the top of the sheet, we see the Availability Summary.
Availability Summary – Employees Needed and Employees Available – Understaffed and Overstaffed days
It shows the following information for each day in our planning period.
Day – If it is a Holiday, you will see HOL. If it is Weekend day, WKD and if it is a working day WRK
Employees Needed: This shows how many employees are needed at work on that day.
If the day is a holiday, then this will be set to 0.
Otherwise, this will be the number of employees needed for that weekday (that we entered in Step 2 of data entry in the Settings sheet)
Employees Available: This reflects how many employees are available to work on the day.
If the day is a holiday, this will be set to 0.
If the day is a weekend, this will be set to 0.
It will take into account, the Employees’ hire statuses, Employees’ work schedules on that weekday and vacations planned by employees .
Employees on Vacation: This is how many employees who are employed on that day, but have taken a vacation.
If it is a holiday, this will be set to 0.
If the day is a weekend, this will be set to 0.
Otherwise, this will reflect the count of employees who have planned vacations for that day.
Availability – Needed: This tells us whether we are understaffed or overstaffed or right-staffed.
This is just the difference between Employees Available and Employees Needed. If it is negative, we are understaffed (availability is less than need). If it is positive, we are overstaffed (availability is greater than need). If it is 0, we have the right number of staff (availability equals need).
We will see a red flag where there is under-staffing.
We can also create a similar flag for over-staffing if needed. We have to edit the conditional formatting rules to do that.
By knowing which days are understaffed or overstaffed, we can take actions accordingly. If we are understaffed, we can either increase capacity by hiring more permanent or temporary employees.
We can also see each employee’s availability on the calendar.
Availability Calendar shows each employees availability and vacation
Green indicates the employee is available to work
Red indicates the employee is on vacation
Purple indicates a holiday
Gray indicates the employee is not working that day (Weekend or the employee does not work on that weekday)
This calendar is set up to handle 50 employees for 366 days.
Step 7: View Report to see monthly summary of availability
The Report sheet is also fully automated. It will show the overall summary and summary of availability by month.
Summary Report for Team Vacation Planner – Understaffed Days by Month
We can see, for each month, the number of days in month, holidays, weekends, working days and understaffed days.
% Days Understaffed = (Understaffed Days / Working Days)
This sheet is set up as print-ready. You can print and share with team. You can also export to PDF and share PDF.
Extending the Team Vacation Planner template
Here are some ways to extend the template and customize further.
How to extend the calendar for more than 50 employees?
The template is designed to handle data for 50 employees by default. The Calendar sheet displays up to 50 employees. If you need for more than 50 employees, it is very easy to extend. Let’s say we want to extend to 55 employees.
Unprotect the Calendar sheet with indzara as password.
Unprotect or unlock sheet using indzara as password
Select rows 59 to 63. Right Click and choose ‘Copy’.
Select 5 rows and copy
Right Click on row 59 (row label) and choose ‘Insert Copied Cells’
Insert the 5 rows of copied cells
You should now see the employees extended to 55.
5 new rows will be added to calendar
How to edit the red flag in the Calendar?
We see a red flag when there is understaffing. We don’t have any flag for overstaffing. If you would like to edit these rules, it is easy.
In the Calendar sheet, select cell D7.
Click on Conditional Formatting menu and choose Manage Rules.
Open Conditional Formatting Rules
Click on Edit Rule.
Conditional Formatting Rules Manager – Icon sets
We can see the rule used.
Edit conditional formatting rule to modify the red flag for understaffed days
You can change the icons used for the three options (+ve number, 0, -ve number).
After changing, Click OK in the dialog boxes and apply changes.
How can I change the colors used in the Calendar?
Click on cell D14
Click on Conditional Formatting menu and choose Manage Rules.
Conditional Formatting rules for calendar
Click on any rule and select Edit Rule.
Change color for any rule
Click on Format to change color. In the image above, I have chosen the rule that applies purple color to Holidays.
After changing colors, click OK in the dialog boxes and apply changes.
How to remove ‘Frozen panes’ in the Calendar sheet?
In the calendar sheet, we have applied ‘Freeze Panes’ to set columns A to C, and rows 1 to 13 always visible. If this is not preferred, you can ‘unfreeze panes’ easily.
In the VIEW ribbon, choose Freeze Panes –> Unfreeze Panes.
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
DETAILED STEPS
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.
Settings – Select Weekends to exclude from working days
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.
Settings – Enter Company Holidays to exclude from working days
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.
PTO Policy General Settings – Sample Illustration – Weekly
START DATE
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.
GENERAL SETTINGS
The general settings apply to both types of PTO (template supports 2). The following inputs are provided.
PTO UNIT
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.
PTO Accrual Period – Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly, 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.
November Month accrual days for a Weekly PTO Accrual Scenario
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.
Weekly PTO Accrual Scenario Illustration
We enter this First Accrual Period Begin Date in the settings.
PTO Manager Excel Template – Settings – First Accrual Period Begin Day
If the First Accrual Period Begin Date is after the Start date, then an error message appears.
Data validation Error when First period begin date after Start date
Please ensure that the First Accrual Period Begin Date is not after the Start Date.
ACCRUAL TIMING
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.
Accrual Timing – Beginning or End of accrual 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.
First Accrual Period Window and Accrual Periods per year for review – Weekly (Beginning)
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.
Twice A Month Accrual Period Setting
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 Window and Accrual Periods per year for review – Twice A 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.
Twice A Month Accrual Period Setting Last Day option
The First accrual window will change accordingly.
First Accrual Period Window and Accrual Periods per year for review – Twice A Month last day
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.
First Accrual Period Window and Accrual Periods per year for review – Monthly Beginning
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.
Number of PTO Types 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.
Monthly PTO Accrual – PTO Balance trend chart
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.
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, 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.
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.
Annual PTO Rollover Policy – Zero Rollover Limit
To understand the impact of this setting, see chart below.
Annual PTO Rollover Policy – Zero Rollover Limit – On Balance Chart
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.
Annual PTO Rollover Policy – Rollover Limit of 4 days
The impact can be visually seen below.
Annual PTO Rollover Policy – Rollover Limit of 4 days – On Balance Chart
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.
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.
PTO Rollover Timing Setting – Options – Calendar Year, Work Anniversary, Custom Date
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.
PTO Rollover timing – Zero Rollover and Calendar Year
The PTO balances are shown below.
PTO Rollover timing – Zero Rollover and Calendar Year – On Balance chart
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.
Custom Date
Let’s review the last option in PTO Rollover timing: Custom Date.
PTO Rollover timing – Zero Rollover and 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.
PTO Rollover timing – Zero Rollover and Custom Date — On Balance chart
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.
Probationary Period and Max PTO Balance Settings 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 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.
Probationary Period and Max PTO Setting in PTO Policy – On Balance chart
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.
PTO Accrual rate varies by Tenure Setting
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.
PTO Accrual rate varies by Tenure Setting – Enter data by Months
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.
Number of PTO Types to track set to Two
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.
PTO Policy Settings for second PTO Type Sick days – On Balance Chart
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.
Sample Employee Data to check balance calculations
In the PTO Report sheet, choose Employee name from the drop down.
Choose Employee from drop down in PTO Report to view accrual rates
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.
Employee PTO Report – Calendar – Review accrual days for accuracy
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.
Employee PTO Report – 2017 Review accrual days for accuracy
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.
PTO Policy Settings for second PTO Type Sick days – On Balance Chart
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.
Employee Data Sample for PTO Manager
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.
Enter Paid Time off dates and Paid Time Off type for each employee
If the PTO unit was chosen as Hours, then PTO Hours column should be entered. An example is shown below.
Enter Paid Time off Hours and Paid Time Off type for each employee
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.
Employee PTO Report – Page 1 – Current PTO Balances and Rates
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.
Employee PTO Report – Page 1 – Accrual Days and PTO Days on Calendar
We can also see the monthly totals of PTO earned and PTO used by the employee.
Employee PTO Report – Page 1 – Monthly totals of PTO Earned and PTO used
The second page of the report has the starting balances information.
Employee PTO Report – Page 2 – Starting Balances, First Effective Accrual period
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.
Prorating when employee starts in middle of an accrual period
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.
Employee PTO Report – Page 2 – PTO Balance and PTO Used – Chart
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.
Paid Time Off Calendar – Sample – Employees paid time off 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.
Monthly PTO Calendar with changed Legend values
The sheet also shows the totals for the month for each employee.
Paid Time Off Calendar – Sample – Employees paid time off – month totals
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.
(Optional) Step 8: Make Adjusments to PTO Balances
If you would like to add or remove PTO, outside the PTO policy settings you have entered, then you can use the Adjustment table.
Adjustments Table to add or subtract pto balances
Enter Employee name, Date, PTO Type and the amount of Adjustment to be made. To increase balance, enter positive value. To reduce balance, enter negative value.
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.
Whether you are a HR Manager, a Small Business Owner or a Project Manager who is responsible for tracking leave/vacation/time-off taken by employees, you will find this simple and effective vacation tracker template useful. This leave tracker Excel template enables vacation tracking in an organized way, calculates employee attendance, presents a calendar view of leave across multiple employees and also prints an annual employee report.
