Employee Timesheet Template – Excel Time card & Work Hours Calculator

Posted on
Weekly Timesheet Template -Printable - Excel Template
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.

Continue reading Employee Timesheet Template – Excel Time card & Work Hours Calculator

Resource Capacity Planner Excel Template – Step by Step User Guide

Posted on

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:

  1. Enter Settings
  2. Enter Resource list with standard availability and allocate Capacity to Skills and Projects
  3. Enter vacation and overtime
  4. Enter Demand (tasks) data
  5. Refresh Calculations
  6. View Dashboard to evaluate plan
  7. Address any over-utilization and under-utilization
  8. 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
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
Enter Weekends

We will choose SAT & SUN as weekends for this tutorial.

Enter Settings - Weekends
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
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
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
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 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
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
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
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
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
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
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
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
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:

  1. 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.
  2. 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
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
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
Refresh All calculations

 

Step 6: Review Dashboard

Dashboard

Dashboard sheet shows the summary of metrics across all projects, skills and resources.

Capacity Planning Dashboard
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
Dashboard Slicers

At the top, you will see the overall summary of metrics.

Capacity Planning Dashboard - Summary 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
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
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
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
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
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.

Capacity Planning Dashboard - Hours - Capacity vs Demand - Charts
Capacity Planning Dashboard – Hours – Capacity vs Demand – Charts

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.

Capacity Planning Dashboard - Cost - Capacity vs Demand - Charts
Capacity Planning Dashboard – Cost – Capacity vs Demand – Charts

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:

  1. Change Capacity
    • Adjust standard availability of resources in the Resources sheet.
      1. Increase (to increase capacity) or Decrease (to reduce capacity) available hours
    • Add overtime (to increase capacity) or vacation time (to reduce capacity)
  2. Change Demand
    • Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
  3. 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
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
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
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
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
Assign Project Planner 2 Development capacity

Important things to note here:

  1. 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.
  2. 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.
  3. When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
    1. 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
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
Assign tasks from Developer 3 to Project Planner 2

Let’s refresh the Dashboard again.

All projects - Resource - Capacity vs Demand - Final
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
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
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
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
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
Calendar Settings – Measure Capacity Demand or Surplus Deficit

 

Unit
The options for Unit are Hours and Cost.

Calendar Settings - Unit Hours or 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
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
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.

Recruitment Manager Excel Template – Most Frequently Asked Question

Posted on

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

  1. Required Dates are missing.
  2. 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
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.

To summarize,

Job Posted Date <= Stage 1 DT <= Stage 2 DT < = Stage 3 DT <= Stage 4 DT <= Stage 5 DT <= Stage 6 DT

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
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
Job ID required in Jobs sheet

 

Required Fields 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 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
Correct Date format check – Example Invalid Date

 

If Excel treats as date, it would appear as shown below.

Correct Date format check
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
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
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
HR Dashboard Summary Metrics

 

  • 3 KPIs related to Open Positions – even if there are no HIRED applications.
Open Positions with 0 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)
Recruitment Funnel (up to 6 stages)

 

  • Monthly Metrics – only if there are HIRED applications.
Monthly Metrics (Hired and Days to Hire)
Monthly Metrics (Hired and Days to Hire)

 

  • Pipeline Efficiency of Hiring – only if there are HIRED applications.
Pipeline Efficiency - Time Spent in each stage
Pipeline Efficiency – Time Spent in each stage

 

  • Application Sources – only if there are HIRED applications.
Application Sources - Hired & Conversion Rate
Application Sources – Hired & Conversion Rate

 

  • Decline Reasons – only if there are NOT HIRED applications.
Decline Reasons for Candidates/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
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.

 


Recommended

How to extend calendar view in PTO Manager Excel Template

Posted on

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
PTO Manager – Calendar View – Default

 

It would stop with 40th Employee.

Calendar shows Employees 1 to 40
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
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
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
Unprotect Sheet

 

Step 2: Select Row 45

Click on 45 to 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
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
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
Click on 87 to select Row 87

 

Step 7: Clear Row 87

Right click and choose ‘Clear Contents’

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

How to increase tracking period in PTO Manager Excel Template

Posted on

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

For more about the template – Product Page

It is designed such that we can continue to use the same file for many years. By default, the tracking period is set to 2 years. This was set so that the file size is smaller, and the calculations are faster. When we extend the tracking period, Excel needs to do a lot more calculations and store a lot more data. So, we limit the default period to 2 years, but provide an easy option for the user to extend the period in few simple steps. In this blog post, we will see how we can easily extend that to more than 2 years.

 

If you prefer video demos, please watch the video.

 

VIDEO DEMO

 

Before we start the process, it is important that we understand the Start Date concept used in the template first.

You can choose to start tracking PTO in any year from any date. By default, the template has 1-Jan-2018 as starting date.

Start Date - Date of tracking PTO
Start Date – Date of tracking PTO

 

You can change this Start Date to any date.

The template will then assume 2 things:

1) PTO balances of employees as of (31-Dec-2017) the date prior to Start Date will be entered in the Employees sheet.

Starting Balances for Employees
Starting Balances for Employees

 

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

Enter PTO for employees from Start Date
Enter PTO for employees from Start Date

 

This is how we ensure that you can use the template to track PTO in companies where you have employees who have been working already. They have already accrued balances and you can just enter them as starting balances and then use just this template to track new PTOs from now onwards.

 

Tracking Period Limit

In some cases, you may have detailed PTO dates for each employee tracked for the past few years and you may want to migrate that detailed data to this template. Or if you have purchased the template in 2017, you would reach the end of default tracking period of 2 years in 2019.

For example, we want to track PTOs from 2010. The Start Date would be 1-Jan-2010.

The PTO Report though will not show current balances as of today (March 2018 when this tutorial was written).

PTO Report not showing Balances
PTO Report not showing Balances

 

And the Balance Trend Chart will only track until 2 years from Start Date.

(To illustrate, I have entered 4000 as the number of days to display on the chart.)

Report Chart shows only 2 years
Report Chart shows only 2 years

 

Now that we understand the limitation, let’s start our process to extend the tracking period.

 

6-Step Method to increase tracking period

 

Overview of steps

  1. Backup file
  2. Unhide hidden CAL sheet
  3. Find the end of table and extend calculations down
  4. Check PTO Report sheet for verification
  5. Hide the CAL sheet
  6. Save changes in File

 

Step 1: Backup

We recommend saving copies (archiving) of your files regularly as there is no other backup system.

Similarly, before making any extensions like we are going to do now, please save a copy of your current file separately with a different name (for example PTO_Manager_Archived_2018_03_18.xlsx) and then use your main file for the following steps.

 

Step 2: Unhide the hidden CAL sheet

Right-click on a sheet name and then choose Unhide.

Right Click on a sheet name and then choose Unhide
Right Click on a sheet name and then choose Unhide

 

Choose CAL and then click OK

Unhide CAL sheet
Unhide CAL sheet

 

Step 3: Extend calculations

Find the end of the calculations table. Click anywhere outside the table. The last cell of the table will appear as shown below.

End of CAL table
End of CAL table

 

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

Hover, Click and Drag Down
Hover, Click and Drag Down

 

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

Extended calculations for 10 years
Extended calculations for 10 years

 

Step 4: Verify

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

PTO Report showing Balances
PTO Report showing Balances

 

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

Balance Trend Chart for 10 years
Balance Trend Chart for 10 years

Step 5: Hide the CAL sheet

Now that we have completed extending the period, we need to hide the sheet.

Right-click on CAL sheet name and choose Hide.

Hide the CAL sheet
Hide the CAL sheet

Step 6: Save the changes

Until we save the file, all the changes made so far will not be retained.

Press Ctrl+S or use the File menu to save the file.

 

Now, we have extended the tracking period to 10 years from the default 2 years.


Recommended 

How to add tiers to the Tenure table in PTO Manager

Posted on

In this blog post, we will discuss the simple 4 step process to add tenure tiers to the Small Business PTO Manager Excel Template.

Small Business PTO Manager Template is a solution to manage employees’ PTO data in Small Businesses

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
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
Step 1 Unprotect the sheet

 

A dialog box pops up where you can enter the password indzara and click OK.

Enter password to unprotect
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
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
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
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
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
Save changes

 

 

If there are any questions about this article, please post them in the Comments below.

Team Vacation Planner Excel Template – Support

Posted on
Summary Report for Team Vacation Planner - Understaffed Days by Month

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.

How to use Excel tables for Data Entry

 

Assuming you know how to enter data in Excel tables, let’s move forward to the overview of steps.

  1. Enter Planning period
  2. Enter Number of Employees needed per weekday
  3. Enter Company holidays
  4. Enter List of Employees and their information
  5. Enter dates of Vacation planned by the employees
  6. View Calendar to see if there are any understaffed days
  7. 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
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
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
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
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
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
Availability Summary – Employees Needed and Employees Available – Understaffed and Overstaffed days

 

It shows the following information for each day in our planning period.

  1. Day – If it is a Holiday, you will see HOL. If it is Weekend day, WKD and if it is a working day WRK
  2. Employees Needed: This shows how many employees are needed at work on that day.
    1. If the day is a holiday, then this will be set to 0.
    2. 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)
  3. Employees Available: This reflects how many employees are available to work on the day.
    1. If the day is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. It will take into account, the Employees’ hire statuses, Employees’ work schedules on that weekday and vacations planned by employees .
  4. Employees on Vacation: This is how many employees who are employed on that day, but have taken a vacation.
    1. If it is a holiday, this will be set to 0.
    2. If the day is a weekend, this will be set to 0.
    3. Otherwise, this will reflect the count of employees who have planned vacations for that day.
  5. Availability – Needed: This tells us whether we are understaffed or overstaffed or right-staffed.
    1. 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).
    2. We will see a red flag where there is under-staffing.
      1. 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
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
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
Unprotect or unlock sheet using indzara as password

 

Select rows 59 to 63. Right Click and choose ‘Copy’.

Select 5 rows and 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
Insert the 5 rows of copied cells

 

You should now see the employees extended to 55.

5 new rows will be added to calendar
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
Open Conditional Formatting Rules

 

Click on Edit Rule.

Conditional Formatting Rules Manager - Icon sets
Conditional Formatting Rules Manager – Icon sets

 

We can see the rule used.

Edit conditional formatting rule to modify the red flag for understaffed days
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
Conditional Formatting rules for calendar

 

Click on any rule and select Edit Rule.

Change color for any 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.

Unfreeze Panes in Calendar sheet
Unfreeze Panes in Calendar sheet

Small Business PTO Manager – User Guide – Step by Step Tutorial

Posted on
Employee PTO Report - Page 1 - Accrual Days and PTO Days on Calendar

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

  1. Enter Weekends and Holidays in the Home sheet
  2. Enter policy inputs in the PTO Policy Settings sheet. Review first accrual window.
  3. Enter one employee’s data in Employees sheet. Review the PTO Report sheet.
  4. Enter list of all employees in Employees sheet
  5. When employee takes PTO, enter PTO info in Time Off sheet
  6. View PTO balance and balance trends in PTO Report sheet
  7. View PTO days on calendar view in Calendar sheet
  8. (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
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
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
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
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
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
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
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
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
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)
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
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 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
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
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
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
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.

PTO Policy PTO Type 1 Settings - Sample Illustration - Monthly
PTO Policy PTO Type 1 Settings – Sample Illustration – Monthly

 

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
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:

  1. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
  2. 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.

Annual PTO Rollover Policy Setting - Options - Zero Rollover, Rollover Limit, Unlimited Rollover
Annual PTO Rollover Policy Setting – Options – Zero Rollover, Rollover Limit, Unlimited Rollover

 

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

Annual PTO Rollover Policy - Unlimited Rollover Limit
Annual PTO Rollover Policy – Unlimited Rollover Limit

 

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.

Annual PTO Rollover Policy - Unlimited Rollover Limit - On Balance Chart
Annual PTO Rollover Policy – Unlimited Rollover Limit – On Balance Chart

 

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.

PTO Rollover Timing Setting - Options - Calendar Year, Work Anniversary, Custom Date
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
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
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
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
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 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
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
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
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.

 

You can enter more or less rows as needed. Read how to enter and delete tenure tiers

 

PRORATING WHEN ACCRUAL RATE CHANGES

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
Number of PTO Types to track set to Two

 

Then, enter details of second PTO Type.

PTO Policy Settings for second PTO Type Sick days

 

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

Employee PTO Report - Calendar - Refresh using Calculate Sheet option
Employee PTO Report – Calendar – Refresh using Calculate Sheet option

 

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

Read how to extend the calendar to show more than 40 employees.

 

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


Small Business PTO Manager Excel Template – Support

Posted on
PTO Manager Excel Template - Product Features

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.

 

Support Articles

Employee Vacation Tracker to track leave & attendance – Calendar & Report

Posted on
Vacation Tracker Dashboard - Excel Template - Leave Calendar view
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.

Continue reading Employee Vacation Tracker to track leave & attendance – Calendar & Report