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.
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 email@example.com with your Order Number. If you need support with the previous version, please email your questions to firstname.lastname@example.org
Overview of steps:
Enter Resource list with standard availability and allocate Capacity to Skills and Projects
Enter vacation and overtime
Enter Demand (tasks) data
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.
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.
We will use a 3 month period from Apr 1, 2019 to June 30, 2019.
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.
We will choose SAT & SUN as weekends for this tutorial.
If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.
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
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.
If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.
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 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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Step 6: Review Dashboard
Dashboard sheet shows the summary of metrics across all projects, skills and resources.
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).
At the top, you will see the overall summary of 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.
To change the currency from US$ to another currency, please follow the steps below:
Press Ctrl+G and select CURR named range.
Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.
Now, the currency on the dashboard display will change accordingly.
Let’s continue with the rest of the dashboard.
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.
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.
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.
In the final page of the dashboard, you can see the charts of Capacity vs Demand in Cost.
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:
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 the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
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.
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.
Let’s refresh the calculations. Data ribbon –> Refresh All.
That will update the dashboard calculations.
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.
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.
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.
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.
Let’s refresh the Dashboard again.
We have no resources over utilized. 🙂
Overall metrics look like below.
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.
There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.
The calendar can be created for the list of resources or list of skill groups or list of projects.
You can select to display Capacity data or Demand data or Surplus/Deficit data.
The options for Unit are Hours and Cost.
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.
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.
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.
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.
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.
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.
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.
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.
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 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.
If Excel treats as date, it would appear as shown below.
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.
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’
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.
3 KPIs related to Open Positions – even if there are no HIRED applications.
Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
Monthly Metrics – only if there are HIRED applications.
Pipeline Efficiency of Hiring – only if there are HIRED applications.
Application Sources – only if there are HIRED applications.
Decline Reasons – only if there are NOT HIRED 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.
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.
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.
This article will present step by step instructions on how to use the Team Vacation Planner Excel template.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Select rows 59 to 63. Right Click and choose ‘Copy’.
Right Click on row 59 (row label) and choose ‘Insert Copied Cells’
You should now see the employees extended to 55.
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.
Click on Edit Rule.
We can see the rule used.
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.
Click on any rule and select Edit 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
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.
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.