PTO (Paid Time Off) Balance Calculator for Hourly Employee

Posted on
Employee Report - PTO Calculator - Hourly Employees - Screenshot

If you are looking for a Simple & Effective solution to calculate and track Paid-Time-Off (PTO) for your hourly employee, you have reached the right place. You can download this free Excel template to track and calculate Employee’s PTO accrual balances based on hours worked by the employee.

If you need a PTO Calculator that calculates accrual based on employee tenure, please see PTO Calculator (Salaried Employees)

If you are familiar with PTO calculation, you would be aware that though it appears to be simple from the outside, accounting for various scenarios and PTO policies make such calculation complex and not easy to implement.

For more details on key components of a PTO policy and how to calculate PTO balances, please read PTO Policy Factors and Balance Calculation

Don’t worry. This Excel template makes all that simple to implement, as all the hard work is already done. You enter the inputs of your PTO policy and are ready to track PTO balance automatically within a few minutes.

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

In this article, we will cover the following topics.

  1. Overview of Features of the Excel template
  2. Download link to the free Excel template
  3. Overview of how to use the template
  4. Detailed Step by Step user guide on how to track PTO for your hourly employee

KEY FEATURES OF THE EXCEL TEMPLATE

  • Options available covers most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates current PTO balance and projected future balance
  • Interactive and printable PTO Report showing all details in a simple calendar view
  • Supports tenure-based accrual rate tiers
  • File is designed for one employee only. Make a copy of workbook to use for second employee.
  • Tracks PTO balance for at least 2 years (and can be reset in new file and extended).

FREE DOWNLOAD

PTO Calculator (Hourly Employee) Excel Template

REQUIREMENTS

Microsoft Excel 2010 or newer

LIMITS

  • Tracking PTO for 1 employee in a file. Please make copy of the entire file to track second employee.
  • 500 Leave Entries

HOW TO USE THE EXCEL TEMPLATE

VIDEO DEMO

OVERVIEW OF STEPS

Before we get into all the details, I want to provide an overview of the 5 simple steps in using the template.

  1. Enter employee details and PTO policy inputs in the PTO POLICY SETTINGS sheet
    • Review first set of accrual days to ensure information is correct. Ensure there are no errors in the data entry validation.
  2. Enter your company’s Weekends and Holidays in WEEKENDS HOLIDAYS Sheet
  3. When employee works different hours (more or less) than the default daily hours, enter them in the HOURS WORKED sheet
  4. When employee takes PTO, enter PTO info in the PTO USED sheet
  5. View PTO balance and balance trends in the PTO REPORT sheet

STEP BY STEP GUIDE

Let’s start from the beginning – in the PTO POLICY SETTINGS sheet.

Settings - Employee Details and Start Date
Settings – Employee Details and Start Date

STARTING DATE

Enter the date from which you would like to use this template to track PTO. There are two key purposes for this date. 1) The template will only calculate PTO from this date. 2) The Starting Balance (which we will discuss soon) will be the balance at the end of the day before this Starting Date.

Employee Details

EMPLOYEE NAME

Enter name of employee for whom we will be tracking and calculating PTO balance. This will automatically then appear in the PTO Report.

HIRE DATE

Enter the date when employee joined the company. Even if you have been tracking PTO using some other tool and now want to switch to use this template, enter the actual hire date of the employee. I will soon explain how you can carry over balance from your previous tool.

STARTING BALANCE

Here, we can enter balance we carry over for employees whom you have been tracking PTO from other tool before you switch to using this template.

For example, if the employee started working in 2017, but you started to switch to this template on July 3, 2019, then you can enter the starting balance as of end of July 2, 2019. Any PTO used from July 3, 2019 will have to be entered in the template.

DEFAULT HOURS WORKED

This is to record how many hours this employee works every working day. This option is given to reduce data entry. Let’s say the employee works usually 8 hours a day but occasionally he/she may work less or more than 8 hours. You can enter 8 as default working hours. On all days except holidays and weekends, the template will assign the default hours worked by employee.

You can then enter adjustments for specific dates when the employee did not work 8 hours.

Instead of entering the actual hours every day, the template allows setting the default hours once and then only entering work hours when it deviates from the default. Less data entry. Simple and Effective.

If your employee has varying hours daily, then you can enter 0 hours as default and choose to enter actual hours for every day (I will show where to enter that information in the upcoming steps below).

That’s how flexible the template is. You can make it work to suit your business requirements.

Now, let’s get into the PTO Policy. Fun Stuff!

Here is a sample policy.

PTO Policy - Hourly Employee
PTO Policy – Hourly Employee

There are several components of a PTO policy. We will go through one by one.

PTO ACCRUAL PERIOD

This is to inform how we often accrue the PTO. We have 4 options here: Weekly, Every 2 Weeks, Twice a Month and Monthly.

PTO Accrual Period - Options - Weekly, Every 2 Weeks, Twice a Month, Monthly
PTO Accrual Period – Options – Weekly, Every 2 Weeks, Twice a Month, Monthly

Weekly

This option means that employee can accrue PTO balance once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy.

PTO Policy - Weekly Accrual
PTO Policy – Weekly Accrual

In the above screenshot, you can see that a new input (First Accrual Date) becomes necessary for weekly policy. Since a weekly accrual can happen on Fridays in some companies and Mondays in some companies, we provide the option to enter a specific date when the first accrual should happen. Here, we entered 5th July 2019 – which is a Friday.

So, the template will set accruals every Friday beginning on 5th July 2019. 5th July, 12th July, 19th July…..

Every 2 Weeks

Entering an Every 2 Weeks policy is similar to weekly. The template, in this case, will only set accruals every 2 weeks.

PTO Policy - Every 2 Weeks Accrual
PTO Policy – Every 2 Weeks Accrual

If the first accrual date is 5th July 2019, the template will automatically set up accruals every other Friday – 5th July, 19th July, 2nd Aug, 16th Aug…..

Twice a Month

Now, let’s move to Twice a Month option. Here, we can enter the two dates of the month the accrual will happen.

PTO Policy - Twice a month Accrual
PTO Policy – Twice a month Accrual

For example, in the screenshot above, we have chosen 1st and 15th of every month.

You have control over the specific 2 days of the month. The template even supports ‘Last Day’ option.

PTO Policy - Twice a month Accrual - Last Day
PTO Policy – Twice a month Accrual – Last Day

For example, the above scenario will set up accrual on 15th of every month and Last Day of every month. If the month has 28 days or 29 days or 30 days or 31 days, the template will automatically figure out the last day and set up accrual for that day.

Monthly

I am sure that by now, you see the pattern here. For monthly, we can set the day of month that we want accrual to happen every month.

PTO Policy - Monthly Accrual
PTO Policy – Monthly Accrual

For example, as shown above, we can do accruals on 1st of every month.

Instead of 1st, you can type in any day of the month. You can also choose ‘Last Day’ of month.

PTO Policy - Monthly Accrual - Last Day
PTO Policy – Monthly Accrual – Last Day

REVIEW FIRST ACCRUAL

We need to check and ensure that the data provided in the inputs are correct and result in a valid accrual policy. The template shows the first set of accrual dates.

Review First Set of Accrual Days
Review First Set of Accrual Days

In the above screenshot, there are no errors. The accrual dates are weekly from 5th July. (Probationary period setting is not considered in this list of dates and the dates shown will include dates in any probationary period you may have set).

The template has in-built data validations to identify if the inputs do not align.

For Weekly and Every 2 Weeks options, First Accrual Date should be within 1 or 2 weeks from the Starting Date. Otherwise, you will see an error message like this one.

Data Validation - First Accrual Date should be within 1 week of Starting Date
Data Validation – First Accrual Date should be within 1 week of Starting Date

In these cases, update the First Accrual Date input to ensure that it is correct.

Now let’s discuss Rollover Policy.

ANNUAL PTO ROLLOVER POLICY

The template provides three options.

Rollover policy options
Rollover policy options
  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year.

With rollover policies, there is another variation. Some companies may apply rollover at calendar year change 1st Jan of every year or on work anniversary dates (which vary for each employee). You can easily change that setting.

Rollover Timing options
Rollover Timing options

PROBATIONARY PERIOD

Enter the duration of the Probationary period in days.

Settings - Probationary Period, Maximum Allowed Balance
Settings – Probationary Period, Maximum Allowed Balance

For example, in the screenshot above, employee does not earn any PTO during the first 90 days of employment.

MAXIMUM ALLOWED PTO BALANCE

You can set a limit on maximum balance at any time, to ensure that there is a limit to PTO accrual. You can set the amount (hours)in the Maximum Allowed PTO Balance field.

ROUNDING IN PTO BALANCE

When the template calculates balances and display in the report, you can control the precision by just entering the number of decimals.

PTO ACCRUAL RATE

This defines how much PTO hours are earned by the employee for hours worked.

Accrual Rate Based on Tenure - One Policy
Accrual Rate Based on Tenure – One Policy

For example, in the above screenshot, the employee would earn 1 hour of PTO for every 40 hours worked.

Since the tenure completed is 0 and we have only tier, the policy will apply to all employees regardless of tenure.

There should always be a record with 0 tenure completed

  • If the employee worked 39 hours, he/she would not earn any PTO. But once the 40th hour is completed, he/she will earn 1 hour of PTO.
  • The 40 hours do not have to be in the same week or month.

If in your company, the employee would earn 4 hours of PTO for every 160 hours worked, then you would enter 4 and 160 in the corresponding cells.

Accrual Rate Based on Tenure - One Policy - Multiples
Accrual Rate Based on Tenure – One Policy – Multiples

In this scenario, for the first 159 hours, the employee does not earn anything. But once the 160th hour of work is completed, then it accrues 4 hours of PTO. Essentially, the employee accrues in multiples of 4 hours.

You can also have a tiered structure where you reward loyal employees with higher PTO accrual rate.

Accrual Rate Based on Tenure - Tiered Policy
Accrual Rate Based on Tenure – Tiered Policy

In the above screenshot, employees with less than 12 months tenure will earn 1 PTO hour per 40 hours worked. Employees between 12 to 24 months tenure will earn 2 PTO hours for same 40 hours worked, while employees above 24 months tenure will earn 3 PTO hours per 40 hours worked.

WEEKENDS & HOLIDAYS

Now, we are ready to enter the company weekends and holidays.

Weekends
Weekends

Choose TRUE for days that are considered weekends in your company.

For example, in the above screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Holidays work similarly, except here you must enter each holiday individually. This is done in the HOLIDAYS sheet.

Holidays
Holidays

For example, in the below screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Read how to enter and delete data in Excel tables

Now we have gone through the various PTO policy input options in the PTO calculator. These settings have to be entered only once for an employee.

HOURS WORKED

In this template entering data on hours worked is simple, as we have already set default hours worked initially. On days when employee worked hours different than the default, then we enter them in the HOURS WORKED sheet.

Enter Hours Worked by Employee
Enter Hours Worked by Employee

It is important to note that you should only enter the adjustment.

For example, if the default hours worked is 8 and if you enter an adjustment of -8, that means the employee worked 0 hours on that day.

If the adjustment is 1, then the employee worked 9 hours on that day.

PTO USED

When an employee takes PTO or plans to take PTO, you can enter that in the PTO USED sheet.

Enter PTO Days taken by employee
Enter PTO Days taken by employee

We can enter date ranges to enter multi-day vacation. Even if it is a single day vacation, please enter both start and date as the same date. All 3 fields (PTO START DATE, PTO END DATE, PTO HOURS) are required entries here.

Multi-day Vacations
It is important to note here that the PTO Hours you enter is the daily value not total for multiple days.

In the above example, 1 hour of PTO for each of the 2 days (May 7th, May 8th) – in total 2 hours – will be subtracted from the PTO balance, assuming those dates are not Weekends and Holidays in your company. However, you should enter 1 and not 2 in PTO Hours column.

Now that we have entered the necessary data inputs, we are ready to view the PTO report.

PTO REPORT

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

Let’s take the report in two sections. The top section shows the summary.

Employee Report - PTO Calculator - Hourly Employees - Summary
Employee Report – PTO Calculator – Hourly Employees – Summary

BALANCES

By default, the report shows today’s PTO balance for the employee. The Projected Balance on a future date can also be shown. You can modify the date and can view PTO balance at any date. That will consider future data (work hours, PTO used, holidays and weekends).

POLICY DETAILS

Policy Details along with key dates are shown so that when you print the report, the policy details are also clearly visible.

The bottom section provides an interactive calendar.

Employee Report - PTO Calculator - Hourly Employees - Calendar
Employee Report – PTO Calculator – Hourly Employees – Calendar

DATES

The Calendar (12 month) view is to show specific details on each date for a period you choose. You can enter the FROM and TO dates. The calendar will then display information for that specific period.

DISPLAY

The display inside the calendar shows the values for each date and you can control what is displayed.

Calendar Display Choices
Calendar Display Choices

You can display Worked (Number of Hours worked by employee on the day) or Balance Change (PTO Balance Accrued on the day) or PTO Used (PTO used by employee on the day).

WORKED

Employee PTO Report - Calendar Hours Worked
Employee PTO Report – Calendar Hours Worked

You can view the number of hours worked by employee on each day. This will include any work hours adjustments you entered.

BALANCE CHANGE

This shows the accrual days and the amount of PTO Balance change (hours) on those accrual days.

Employee PTO Report - Calendar - Balance Change
Employee PTO Report – Calendar – Balance Change

PTO USED

Employee PTO Report - Calendar - PTO used
Employee PTO Report – Calendar – PTO used

This will reflect the dates when the employee takes PTO and the number represents the number of hours of PTO used.

MONTHLY BALANCES

On the right side you can view the Monthly starting PTO Balance and Monthly Ending PTO Balance. Please note that all balances reflect at end of day.

MONTH-END BALANCE TREND

At the bottom of the report, the month-end balance trend chart shows the balances month over month.

PRINT OR EXPORT

You can Print the report or export to PDF and share.

BALANCE ADJUSTMENTS

If you would like to add or remove PTO to the balance outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

For example, if you would like to reward employee with extra PTO, you can enter here as a positive adjustment.

Balance Adjustments
Balance Adjustments

This sheet can be ignored if you don’t need to make such adjustments.

 

I hope this free Excel template is useful to track Paid Time Off (PTO) for hourly employee in your organization.

Does this template address policy settings in your organization? Does this save time in calculating PTO balances? Do you have any suggestions on how this template can be improved further?

Please provide your feedback in the Comments below. Thanks for your support.

 


RECOMMENDED TEMPLATES

Employee Timesheet 2020 – Excel Time card & Work Hours Calculator

Posted on
Employee Timesheet Exclel 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.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Employee Timesheet in Google Sheets.

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

2020 Leave Tracker & Vacation Tracker Free Excel Template

Posted on
Leave Tracker & Vacation Tracker
The Leave Tracker Excel template enables leave/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. 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 leave/vacation tracker template useful.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Employee Leave Vacation Tracker in Google Sheets.

Continue reading 2020 Leave Tracker & Vacation Tracker Free Excel Template

New Employee Checklist 2020 – Free Excel Template

Posted on
New Employee Checklist

This Excel template is designed to document completion of activities in a checklist. HR departments in companies can use this to track or document new employee on-boarding activities. Typically, there is a list of activities that each employee has to complete to ‘on-board’ to the new company. We can use this one file to track such on-boarding for all employees.

The key features in this template are

  • Customize activities for your business (maximum 12 activities)
  • Printable checklist for each employee
  • Choose whether to print dates
  • Customize header with ordering fields as you prefer
  • Rename fields as needed (except the activity dt fields)
  • Customize footer with your business information
  • % Complete progress bar
  • All employees’ data in one file

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit New Employee Checklist in Google Sheets.

Download New Employee Checklist 2020

REQUIREMENTS: Microsoft Excel for Windows (2010 or above); Microsoft Excel for Mac (2011 or above);

VIDEO DEMO

 

HOW TO USE

The template has three sheets – Settings, Employees and Print. It uses Excel tables feature to store information.

If you are new to Excel Tables, please see article (with video) on Excel tables.

Settings

In the Settings sheet, let’s enter company name. If we don’t want to print activity dates, then choose No to that.

Finally, enter the list of activities.

New Employee checklist - Settings
New Employee checklist – Settings

 

Employee Data

In the Employees sheet, let us enter each employee and their information.

Tip: If you would prefer to print empty checklists and check items in the hard copy, then skip entering the Employee Data and directly move to Print step below.

 

New Hire Checklist – Employee Data
New Hire Checklist – Employee Data

 

These fields can be renamed as needed.

Then, we enter the activity completion dates for each employee, as they complete the activities.

New Hire Checklist – Employee Activity completion dates
New Hire Checklist – Employee Activity completion dates

Please do not rename these date fields, as they will impact the formulas used.

 

Print

In the Print sheet, we can print the certificate for each employee separately by choosing the employee name.

New Employee Checklist – Header – Choose Employee
New Employee Checklist – Header – Choose Employee

 

We can change the fields in the header by using the drop down. This allows us to re-order fields to suit our needs. Also, if you had renamed the employee fields earlier, they will automatically appear here. 🙂

If you need to change the format of any of the cells (for example date to text or text to date), just select the cell and press Ctrl+1 to open the format cell dialog box.

New Employee Checklist - Header - Customize
New Employee Checklist – Header – Customize

 

There is no need to change the Activities section of the certificate.

New Employee Checklist – Activities List
New Employee Checklist – Activities List

 

At the end of the activities section, a Checklist Completed Dt will appear if the employee has completed all activities.

 

We can customize the footer area by providing contact information for your business and any internal links for reference for new employees. There are no formulas here. We can just type directly.

New Employee Checklist – Customize Footer
New Employee Checklist – Customize Footer

 

If you prefer to not have anything, you can select the cells, right click and clear contents.

New Employee Checklist - Clear Footer
New Employee Checklist – Clear Footer

 

Now that you have the certificate customized, we can print (Press Ctrl+P to print) or export to PDF using Excel’s built-in options.

The following are a couple of sample certificates printed.

Sample Employee Certificate – Employee checklist
Sample Employee Certificate – Employee checklist

 

Another Sample Employee Certificate - Employee checklist
Another Sample Employee Certificate – Employee checklist

You May also like the following Excel Templates:

Recruitment Tracker – Free HR Excel Template

Recruitment Manager – Excel Template

Employee Timesheet Template

Recruitment Tracker 2020 – Free HR Excel Template for Recruiters

Posted on
Recruitment Tracker Excel Template

Are you in the process of recruiting for an open job/position? If you are looking for a simple and effective solution to manage the recruiting process and truly understand its performance via effective Recruiting metrics, I have good news for you.

Download this simple Excel template to track status of the applicants (candidates) in an organized way. Get an instant view of how many applicants are in the pipeline and where in the funnel applicants are dropping off. You can also easily view the top reasons why candidates were not selected.

Recruitment Tracking – Simple & Effective.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Recruitment Tracker in Google Sheets.

Recruitment Dashboard Report - Summary after Hiring
Recruitment Dashboard Report – Summary after Hiring

Download Recruitment Tracker Excel Template 2020

Download Recruitment Tracker Excel Template 2020 with Sample data

If you are looking for a Google Sheets version, please see Recruitment Tracker for Google Sheets.

VIDEO DEMO

To manage multiple jobs with more features, please see the new Recruitment Manager Excel Template.

This template is very easy to use. Please follow the steps below.

STEP 1: Enter Job Details

Start by entering the Job Title, Recruiter Name and Job posted date.

Recruitment Dashboard Report - Summary after Hiring
Recruitment Dashboard Report – Summary after Hiring

STEP 2: Enter the four stages of recruiting

The four stages of hiring are Application, Phone Screen, Interview and Offer. the fifth stage not mentioned in the offer acceptance (hire) stage. You can change the names of these four stages. If you only have three stages, then leave a blank in the fourth stage name.

STEP 3: Enter Applicants’ information

Enter Applicants or Candidates Information - Recruitment Tracker 2020
Enter Applicants or Candidates Information – Recruitment Tracker 2020
  • Enter Applicant (Candidate) name and dates when the applicant reached each of the four stages. If the applicant does not reach a stage, leave it blank.
  • When a decision is made to not proceed with an applicant, enter CLOSED in the STATUS column.
  • Enter ‘decline reason’ for candidates who were not selected.
  • Enter Comments if needed.
  • STAGE is a calculated field that tells which stage an applicant is.
Enter Applications’ Status and Comments – Recruitment Tracker 2020
Enter Applications’ Status and Comments – Recruitment Tracker 2020

STEP 4: Enter Hired Applicant Name

After a candidate accepts the offer, then enter Hired Applicant and Hired Date information.

Recruitment Tracker 2020 - Enter Hired Applicant and Date
Recruitment Tracker 2020 – Enter Hired Applicant and Date

SUMMARY DASHBOARD

As you enter data, the summary section on the top right updates automatically with the key metrics.

When the job is not filled yet, the metrics will display as below.

Hiring Tracker - Report - Summary During Hiring Process
Hiring Tracker – Report – Summary During Hiring Process

The status will be displayed on the top left as HIRING.

Number of Applications and Days to Hire (or Days in Hiring) will be displayed at the top.

Hiring Tracker – Number of Applications and Days in Hiring
Hiring Tracker – Number of Applications and Days in Hiring

Recruitment/Candidate Pipeline

You can see the active pipeline (applicants or candidates whose application status is not CLOSED). For example, in the image below, there is 1 candidate in APPLICATION stage, 4 in PHONE SCREEN, 1 in INTERVIEW and 1 in OFFER stages.

Active Pipeline - Recruitment Pipeline
Active Pipeline – Recruitment Pipeline

Recruitment Funnel

As you are hiring, the funnel will represent only applicants whose status is CLOSED.

Recruitment Tracker Excel Template – Recruitment Funnel
Recruitment Tracker Excel Template – Recruitment Funnel

After a candidate has been hired, the Summary Dashboard changes slightly automatically to reflect the recruiting metrics. You can record that hiring is complete by entering the Hired Applicant name and Hired Date information.

Recruitment Dashboard Report - Summary after Hiring
Recruitment Dashboard Report – Summary after Hiring

The summary will display the status as HIRED and will show the Recruitment metrics – Number of Applications and Days to Hire.

Recruitment Tracker – Number of Applications and Days to Hire
Recruitment Tracker – Number of Applications and Days to Hire

After hiring, the recruiting funnel represents all applicants (not just Closed status). This will help identify where the candidates fell off in the funnel stages.

Recruitment Tracker Excel Template – Recruitment Funnel after hiring
Recruitment Tracker Excel Template – Recruitment Funnel after hiring

The top 3 decline reasons are shown along with %. This is based on applicants where status is CLOSED.

Top Decline Reasons - Recruitment Tracker
Top Decline Reasons – Recruitment Tracker

Do you find this template useful? Are there any additional scenarios that would be good to incorporate? Is there anything obvious I have missed? Please share your thoughts in the comments below.

If you like the template, please share with your friends.


Do you need to manage recruiting of multiple jobs in one file?

If you are looking to manage multiple jobs and get advanced reporting, please try Recruitment Manager Excel Template

Recruitment Manager Excel template
Recruitment Manager Excel template

How do you keep track of Recruitment in Excel?

After publishing the free template, I received many requests for step by step instructions/tutorial on how to build this recruitment tracker template and track recruiting in Excel? I developed a 4-part video series explaining every single step of creating this recruitment template.  Please watch the video playlist below.

Watch on YouTube

How do you keep track of an Employee joining a company using Excel?

Recruitment process of an employee doesnt stop at receiving the offer, it can only complete if the employee successfully joins for work in the company. During the initial stage every employee has to go through a defined process before joining office. Use a simple checklist to track the employee process.

You May also like the following Excel Templates:

Employee PTO Tracker & Calculator 2020 – Free Excel Template

Posted on
Employee PTO Tracker - Salaried

Do you want to easily find out how many days of PTO (Paid Time Off) you have available? Wondering if there is a simple spreadsheet that can be used as vacation tracker and PTO vacation accrual calculator? You have come to the right place – Employee PTO Tracker Excel Template. You can download this free Excel template to track and calculate Employee’s PTO (or leave or vacation) accrual balances.

This template is designed to calculate PTO balances where PTO is accrued based on tenure. If you are looking for a PTO calculator for hourly employees where PTO is accrued based on hours worked by employee, please visit PTO Calculator (Hourly Employees).

KEY FEATURES OF PTO CALCULATOR

  • Several settings available to cover most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates PTO balances for today and any future date
  • Vacation dates can be entered as date ranges
  • File is designed for one employee only. Make a copy of the workbook to use for the second employee.

If you would like to manage PTO for multiple employees, please visit Small Business PTO Manager Excel template.  

 

Employee PTO (Paid Time Off) Calculator v2 – PTO Balance
Employee PTO (Paid Time Off) Calculator v2 – PTO Balance

Download Employee PTO Calculator & Tracker 2020

VIDEO DEMO

 

HOW TO USE PTO CALCULATOR

  1. Enter inputs in the Employee PTO sheet

    Monthly Accrual PTO Calculator - Inputs to Template
    Monthly Accrual PTO Calculator – Inputs to Template

     

  2. Review the PTO Policy and first accrual window details

    Monthly Accrual PTO Calculator - Review PTO Policy
    Monthly Accrual PTO Calculator – Review PTO Policy

     

  3. Fix if there are any data validation errors

  4. When employee takes PTO, enter PTO info

    Enter Vacation rates in PTO Calculator
    Enter Vacation rates in PTO Calculator

     

  5. View PTO balance and balance trend

    View PTO Balance calculated by Template
    View PTO Balance calculated by Template

 

COMPONENTS OF PTO POLICY

Though the template is very simple to use, there are quite a few terms to understand and several calculations that happen behind the scenes. Let’s start from the beginning. Let’s start with the simple terms first.

User Inputs on PTO Policy
User Inputs on PTO Policy

 

EMPLOYEE NAME
This does not need any explanation. Enter name of employee for whom we will be tracking and calculating PTO balance.

HIRE DATE
A lot of the calculations for employee’s PTO balance depends on the Hire date of employee. Just enter Hire date. Even if you have been tracking PTO using some other tool and now want to use this template, enter the actual hire date of the employee. Tenure (how long an employee has been with the organization) is calculated from the hire date and companies may have tenure based increase in PTO.

PTO UNIT
We can choose to track employee PTO in units of days or hours. If we choose Hours, we have to 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.

ANNUAL PTO ACCRUAL RATE
Annual Accrual Rate is the PTO that an employee accrues in one year. For example, a company may offer 120 hours of PTO per year.

PTO ACCRUAL PERIOD
This is to inform how we accrue the annual PTO rate. Continuing with the above example of 120 hours per year, how will the employee receive these 120 hours. We have 6 options here: Weekly, Every 2 Weeks, Twice a Month, Monthly, Quarterly and Annual.

 

PTO Accrual Period Options - Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual
PTO Accrual Period Options – Weekly, Every 2 Weeks, Monthly, Twice a Month, Quarterly and Annual

 

Let’s see how a Monthly scenario would work.

PTO Accrual Frequency and Annual PTO Accrual Rate
PTO Accrual Frequency and Annual PTO Accrual Rate

 

120 hours will be given to the employee at 10 hours each month for 12 months.

 

FIRST ACCRUAL PERIOD BEGIN DAY and ACCRUAL TIMING
In order to discuss the next two terms, we need to take an example. Let me use a Weekly accrual example to demonstrate.

Weekly PTO Accrual Example Inputs for Template
Weekly PTO Accrual Example Inputs for Template

 

In this example, the employee’s hire date is Jan 1st, 2020. Employee’s Annual PTO accrual rate is 120 hours and that is accrued weekly.  When we think of accrual periods, we have to think of a window with a start date and an end date.
Let’s enter First Accrual Period Begin Date as Jan 1st, 2020 (this is user input). So, the first accrual period window will be 1st Jan to 7th Jan.
When does the employee receive the accrued PTO? Is it on 1st or 7th? This can be controlled easily. In the above example, we have chosen ‘End of accrual period’. So, the employee receives the PTO accrued on 7th Jan.

We don’t have to remember all these calculations because that’s why we use such a PTO calculator tool. 🙂  Let’s review the policy as calculated by the template.

Weekly PTO Accrual Example - Review Policy
Weekly PTO Accrual Example – Review Policy

 

The Policy shows that the employee will accrue 2.308 hours per week. The first accrual window is 1st Jan to 7th Jan. First accrual day where PTO will be awarded to the employee is 7th Jan. The amount on that day will be 2.308 hours. This amount is the same as the weekly rate, because the employee starts on 1st Jan and the weekly window also begins on 7th.
We all know that employees can start in a new job on any day. So, let’s take the same example but for an employee who started on 3rd Jan.

Weekly PTO Accrual Example where Employee Starts in the middle of accrual window
Weekly PTO Accrual Example where Employee Starts in the middle of accrual window

 

We can see that the first valid accrual window is still 1st Jan to 7th Jan, and the accrual happens in 7th Jan (end of window).  However, the amount if only 1.648 hours because the employee only accrues for 5 days (3rd Jan to 7th Jan). Thus the template can easily prorate the PTO awarded when an employee joins in the middle of an accrual window.

The approach is the same for Weekly, Every 2 Weeks, Quarterly and Annual accrual frequencies. Twice a Month and Monthly are slightly different.

 

TWICE A MONTH

For Twice a Month, we don’t need to provide First Accrual Period Begin Date. We will enter 2 days.

Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’
Twice a Month PTO Accrual – Enter 2 days. 2nd day can be ‘Last Day’

 

The template will then take those two days as the accrual days every month. You can 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.

 

MONTHLY

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.

Monthly PTO Accrual – Input day of month – First day example
Monthly PTO Accrual – Input day of month – First day example

 

The ‘Last day’ will be accounted for, correctly whether it is 28th (Feb) or 29th (Feb – Leap year) or 30th or 31st.

Monthly PTO Accrual – Input Day of Month – Last Day Example
Monthly PTO Accrual – Input Day of Month – Last Day Example

 

Now, let’s look at some more options we have with setting PTO/Vacation policy.
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, very high balance could mean more cash out the door 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.

PTO Rollover Policy Settings - Zero Rollover, Rollover Limit, Unlimited Rollover
PTO Rollover Policy Settings – Zero Rollover, Rollover Limit, Unlimited Rollover

 

  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. 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.

 

To see this in action, I have chosen a 40 hour roll over limit.

PTO Rollover Limit - Example - Monthly Accrual Rate
PTO Rollover Limit – Example – Monthly Accrual Rate

 

At the end of the year, the employee’s PTO balance comes from 120 to 50. It is 50 because 40 is carried over and the employee accrues 10 hours for January of next year.
Now with this rollover policy, there is another variation. Companies may apply rollover at calendar year change or on work anniversary dates. You can easily change that setting.

PTO Rollover timing can be Calendar year or Work Anniversary
PTO Rollover timing can be Calendar year or Work Anniversary

 

The next section covers the remaining options in PTO policy.

More PTO policy settings and options - Probationary period, Maximum allowed PTO and tenure based PTO
More PTO policy settings and options – Probationary period, Maximum allowed PTO and tenure based PTO

 

PROBATIONARY PERIOD
In some roles, employees may not be awarded any PTO for the first X number of days. For example, employee does not earn any PTO during the first 30 days of employment. You can set that easily in this template.

 

MAXIMUM ALLOWED PTO BALANCE
The rollover limit only applies to the end of the year balance. Some companies can set a limit on maximum balance at any time. We can set the amount in the Maximum Allowed PTO Balance.

 

ACCRUAL RATES VARY BY TENURE
Companies increase the annual accrual rate for employees who stay with the company for more years. We can handle such scenarios as well. We would choose YES to this first and then fill out the table below.

Employee Vacation Accrual - Annual Rate increased by Tenure
Employee Vacation Accrual – Annual Rate increased by Tenure

 

We can set the Annual PTO Accrual rate and Maximum PTO balance. In the example above, the employee will receive at the rate of 56 hours in the first year, then rate of 106 hours in the second and third year, 144 hours in years 4 to 10.

Important: Please make sure that the first entry here is for 0 completed years.

You can enter more rows as needed.  Read how to enter and delete data in Excel tables

Now we have gone through the various input options in the PTO calculator. These settings have to be entered only once for an employee. After these are finalized, we will enter PTO dates whenever an employee is taking vacation.

 

PTO OR VACATION DATES

 

Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day
Enter Vacation dates in PTO Calculator – Enter range of dates and hours for each day

 

If we track PTO in hours, we have to enter the PTO hours column. We can ignore it if our PTO unit is days. We can enter date ranges to enter multi-day vacation. However, if it is a single day vacation, please enter both start and date as the same date.
In the above example, 3 hours of PTO for each of the 3 days (June 2, June 3 and June 4) – in total 9 hours – will be subtracted from the PTO balance. 2 hours will be subtracted on 5th July.
You can enter more vacations by just typing new row of data in the table.

 

PTO BALANCE
As we enter PTO dates, the balances get updated.

Current PTO Balance shown by default and PTO Balance shown from Hire Date
Current PTO Balance shown by default and PTO Balance shown from Hire Date

 

By default, today’s PTO balance is shown at the top. You can modify the date and can view PTO balance any date. To put it back to today, just type =TODAY().
Similarly, the balance trend chart shows data by default from Hire Date of Employee. You can edit and modify that as well.
Enter the number of days to control the duration displayed in the chart

 

PTO ADJUSTMENT

If you would like to add or remove PTO, outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

Make positive and negative adjustments to PTO Balance easily
Make positive and negative adjustments to PTO Balance easily

 

An example would be an employee who has been with the company for a few years. You were using some system to track the PTO balance and now you want to migrate to this template. You don’t have to enter all the vacation dates from the past. You can just enter the adjustment amount to bring the current balance to the correct amount. If the employee has taken 60 hours of PTO already, then enter -60 as adjustment.

 

PRORATING WHEN ACCRUAL RATE CHANGES
As we had discussed earlier, the accrual rate can vary by employee tenure. If the work anniversary happens to be in the middle of an accrual window, then we have to prorate the PTO accrued.

Let’s take an example where an employee’s hire date is Jan 16th 2019. Accrues 10 hrs a month in 1 year and then 20 hrs a month in 2nd year. So, for Jan 1, 2020, he will earn 15.16 hrs. 15 days at the rate of 10 hrs per month and 16 days at the rate of 20 hrs a month.

The template does this prorating calculation by default.

 

DATA VALIDATIONS

When you enter the First Accrual Period Begin Date, if it is earlier than or after the first valid accrual window, an error will appear. Let’s look at an example.

 

Data Validations built in the template – Example Inputs
Data Validations built in the template – Example Inputs

 

Though the  employee starts on Jan 1st, 2020, we have entered Dec 1, 2019 as First Accrual Period Begin date.

First Accrual Period Begin Date is too late
First Accrual Period Begin Date is too late

 

Employee is eligible to accrue from Jan 1, 2016, but the first accrual window is Dec 1, 2019 to Dec 7, 2019. If the employee will not accrue any balance from Jan 1st to Dec 1st, it is incorrect. This is due to the data entry error. Once we update the First Accrual Period Begin Date correctly, error will go away.


I would like to hear your feedback. Have I missed any of the scenarios that happen in your workplace? Do you find this useful? Please leave your comments below. Please share with your friends.

RECOMMENDED TEMPLATE

PTO Manager Excel Template - Product Features
PTO Manager Excel Template – Product Features


 You May also like the following Excel Templates:

Employee Vacation Planner 2020 – Free Excel Template

Posted on
Employee Vacation Planner

This Excel Template is designed to help assess coverage of team members’ availability during a month. As a Team leader or a Manager of a team, it is important to know the staff coverage available every day, especially during the holiday season. This Excel template can be used every month to ensure that you are aware of exact coverage and plan accordingly. It can handle up to 20 employees.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Employee Vacation Planner in Google Sheets.

Dashboard Summary for Employee Vacation Planner showing daily availability
Dashboard Summary for Employee Vacation Planner showing daily availability

Download Employee Vacation Planner 2020

VIDEO DEMO

 

INPUTS

This Excel template is very easy to use. The following are the inputs needed:

Inputs needed for Employee Vacation Planner template
Inputs needed for Employee Vacation Planner template

 

  • Start Date
    • The template builds out the calendar for 31 days beginning from Start Date
  • # of Employees needed daily
    • This is used to identify understaffed (low coverage) days. Understaffed day is a company working day where the ‘# of employees at work is less than the Number of Employees needed daily’.
  • Weekends
    • Select the weekend days of your company. These days will not be counted as working days.
  • Holidays
    • Enter up to 7 holidays. These days will not be counted as working days.
  • Vacation Dates of employees
    • Enter Employee Name and the planned vacation dates.
    • If an employee plans to take two periods of vacation (Oct 2 to Oct 7 and then Oct 20 to Oct 24), enter that as two rows.
    • If an Employee does not plan to take any vacation, then enter just the Employee name in a row and leave the date columns blank. It is important that all employees appear at least once in the table.

 

DASHBOARD

Once the inputs are provided, the template automatically calculates the summary metrics.

Dashboard Summary for Employee Vacation Planner showing daily availability
Dashboard Summary for Employee Vacation Planner showing daily availability

 

  • # of Employees
  • # of Working Days
  • # of understaffed days

In addition it also shows the number of employees at work for each of the 31 days. Holidays and Weekends will not display any numbers. Red bars indicate understaffed days (days where number of employees working is less than the number of employees needed).

 

CALENDAR

The template also has a vacation calendar that visually shows the vacation days on a calendar. Let’s take a closer look at the top of the calendar first.

Employee Vacation Planning Template - Calendar View
Employee Vacation Planning Template – Calendar View

 

Each employee is listed on the left. Holidays are shown in Orange and Weekends are also highlighted in gray. Employee’s vacations are shown in Green fill.

 

The calendar displays for 31 days and 20 employees at a time.

Here’s a look at the entire calendar (vertically).

Employee Vacation Planning Template - Calendar View
Employee Vacation Planning Template – Calendar View with Summary and Legend

 

At the bottom of the calendar, we can see the legend and daily summary metrics. Number of Employees on Vacation and Number of Employees at Work for each day are summarized.

 

PRINT OR EXPORT TO PDF

You can also easily print this for use within your office. Or you can export to PDF and share by email.


RECOMMENDED TEMPLATES

TEAM VACATION PLANNER

If you are looking for a longer planning period, with support for more employees and unlimited holidays, check out the Team Vacation Planner template.

Team Vacation Planner - Excel Template - with more features
Team Vacation Planner – Excel Template – with more features

 

RESOURCE CAPACITY PLANNER

If you are looking for capacity planning in terms of hours of availability and have varying demand, please visit Resource Capacity Planner Excel Template.

  • Supports 100 employees and 30 skill groups
  • Planning period of up to 1 year
  • Handles daily/weekly/monthly demand
  • Automated Summary and Employee Reports

You May also like the following Excel Templates:


I would love to get your feedback on this template. Do you find this useful? What features should be added to this template? Please share in the comments below.