This product is a bundle of 8 individual Excel Templates. The following are links to those individual templates. Please visit them for help material around each of them. If you have questions about the bundle, please leave them in the comments below.
In this article, we will explain step by step how to use the Retention Dashboard Google Sheet Template. To learn more about the features of the template and to download, please visit the Product page.
OVERVIEW OF STEPS
Enter data in Employees Data sheet
Refresh Calculations
View/Interact with 3 Dashboards
STEP 1: ENTER DATA IN EMPLOYEES DATA SHEET
All the input happens in one sheet. This is a table with default 1000 rows.
Employee Data Entry Table
Each row in this table represents one employee’s record.
Start by entering Employee Name, Employee ID and Email information.
Employee ID is a required field.
You can rename EMAIL field and use it to store any other information as needed.
Now, let’s look at 14 other columns in this table. We can categorize them into 3 categories of attributes.
Types of Attributes
4 Employee Attributes – Date of Birth, Hire Date, Gender, Hire Source
Employee Attributes – Date of Birth, Hire Date
These attributes are typically those that do not change over time for an employee
Hire Date and Date of Birth columns should not be repurposed. They are used for calculating Employee Tenure and Age. Modifying this will break the calculations.
Gender and Hire source can be renamed and repurposed. Repurposed means the column can be used to store other information. For example, you can rename Hire Source as ‘Salary Method’ and note values on how the employee gets paid (Check/Cash/Direct Deposit). They will still flow through to the dashboards and be displayed as expected.
These attributes are required to be entered only for employees who have left the company. They can be left blank for active employees.
Exit Date and Exit Type cannot be repurposed as calculations are based on those two columns.
Exit Type requires Voluntary and Involuntary as values.
Also called as Termination Type in some companies.
Exit Reason can be repurposed. It will appear on Exit Dashboard automatically.
Also called as Termination reasons in some companies.
In General values are not limited in any column except Exit Type column.
Exit Type values – Voluntary, Involuntary
When to enter what data?
To begin: When you first start using the template, you will enter all existing employees in your organization.
After that, when new employees join, you will enter that new employee’s information in the table.
When employees move from one department to another or from one location to another, you can just update the specific employee’s data in the table.
When employees leave the company, enter the Exit Attributes for those employees.
How to rename and use customizable fields
In the Employees Data sheet, just click on the column header and rename it.
How to add more attributes
Adding new columns
You can click on cell R3 and type a new column name. This will add a new column. You can add any number of such columns to track information about employees.
Note: new columns will not appear on dashboards automatically.
Step 2: REFRESH CALCULATIONS
The template uses pivot tables and hence the calculations and refreshes every time data is added or edited in the Employees Data sheet.
STEP 3: DASHBOARDS
Once the data is entered and refreshed, we can view the 3 dashboards.
The template provides three automated dashboards:
Retention Dashboard
Retention Dashboard presenting 7 KPIs with trend over the last 12 months.
Employee Retention Dashboard
7 KPIs
Following 7 KPIs are automatically calculated by the template
Active Employees: Number of employees active with the company
Hires: Number of new employees hired
Exits: Number of employees who have left the company
Turnover Rate: (Number of Exits/Avg. Number of active Employees during the period) * 100
Retention Rate: (Number of Exits from Employees active at the Beginning of the period /Number of Employees active at the beginning of the period) * 100
Tenure of employees: Average duration of employment of active employees
Tenure of Exit Employees: Average duration of employment at the time of exit
Snapshot Dashboard
Snapshot Dashboard provides interactive view of # of Employees and Avg. Tenure by 7 different employee and job attributes.
Page 1 shows the number of employees and breakdown by various attributes.
Company Snapshot Dashboard
Page 2 shows the Average Tenure of employees and breakdown by various attributes.
Company Snapshot Dashboard – Tenure
There are 10 Slicers available to drill down.
Dashboard Slicers
To select multiple values in a slicer (for example, Hire Source LinkedIn and Indeed) just press Control while clicking on the specific values.
Exit Dashboard
Exit Dashboard provides interactive view of exit data. Exit Type (Voluntary/Involuntary) and Exit Reason can be quickly viewed to understand how, why and when employees are leaving the company.
Employee Exit Dashboard
12 slicers are available to drill down or filter.
This dashboard represents only employees who left the company.
Modifying Dashboards
Charts: If you do not need one of the charts in the Dashboard, just remove that one chart by selecting and deleting, without impacting the rest of the dashboard.
Slicers: If you do not need one of the slicers in the Dashboard, just remove that one slicer by selecting and deleting, without impacting the rest of the dashboard.
Renaming Slicers: You can rename the slicers by changing the settings of the slicer.
Right click on a slicer and choose ‘Edit Slicer’ modify the title in the Customize option.
Modifying Slicer Settings
Resizing Charts: Depending on the data in your organization, you may need certain charts and slicers to be long or short. Not a problem. Just unprotect the sheet and resize the chart to fit your needs. It will not impact the dashboards in any negative way.
How to Print or Export to PDF
The Dashboards are set up to be ready for print. Press Control+P or use Google sheet’s file menu to print. You can also use Google Sheet’s default Download to PDF option and Download as PDF.
Unprotecting Sheets
You would not need a password to use the template. The template performs the default functionality without having the need to unprotect protected sheets.
How to unprotect sheet?
Whenever you unprotect the sheet, please protect it after making the adjustments.
Extending beyond 1000 employee limit
The template has a hidden sheet named HELP which is limited to 1000 rows. That table needs to be extended down to more rows. Then, automatically the dashboards will pick up more than 1000 rows entered in the Employees Data sheet.
If there are any questions on this template, please post in the comments section below.
This article will present step by step instructions on how to use the Team Vacation Planner Google Sheet template.
Purpose
This Google Sheet template is designed to help calculate the number of employees available on any day and compare with the 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 the Steps
Enter Planning period
Enter Number of Employees needed per weekday
Enter Company holidays
Enter List of Employees and their information
Enter dates of Vacation planned by the employees
View Calendar to see if there are any understaffed days
View Report to see summary of understaffed days by month.
VIDEO DEMO
Detailed Step by Step Instructions
Step 1: 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
Step 2: Number of Employees needed for Weekday
In some companies, the number of employees needed at work can be the same every day. However, in other companies, there may be a varying need based on the day of the week.
For example, in a company that provides Customer Service 7 days a week, more customers may be calling in during Saturday/Sunday compared to other weekdays. In such a scenario, we need more employees at work during Saturday/Sunday.
This template allows you to customize to handle such scenarios that easily. For each weekday, we can set the number of employees needed.
Set Number of Employees needed per weekday
If we enter 0 as Employees Needed, then the template assumes that as weekend day. In the above image, Saturday and Sunday will be considered as Weekends (for reporting purpose).
During weekends, employees will not be available to work.
Step 3: Enter List of Company Holidays
Enter the list of holidays in your company.
Enter list of Holidays in company
Step 4: Enter List of Employees and their Information
We enter the information about employees in the Employees table.
Enter list of Employees, their employment dates and work schedule availability
We enter the name of employee, hire date and termination date (if the employee has left the company).
For example, Employee 1 does not work on Saturdays , but other employees do. We can just enter No for any weekday an employee doesn’t usually work.
Step 5: Enter Dates of Vacation Planned by the Employees
In the Vacations sheet, we enter the vacation planned by employees.
Enter Vacation dates of Employees – Vacation Start and End Dates
This is very simple. Enter Employee name, Vacation Start and Vacation End dates.
If an employee takes only one day off, please enter same date as Start and End dates. Please do not leave the End date empty.
As the employees takes more vacation, just add new rows to this table.
There is no limit to how many vacations can be entered.
This completes our data entry steps.
Step 6: View Calendar to see availability by day.
The Calendar sheet is fully automated.
At the top of the sheet, we see the Availability Summary.
It shows the following information for each day in our planning period.
Day – If it is a Holiday, you will see HOL. If it is Weekend day, WKD and if it is a working day WRK
Employees Needed: This shows how many employees are needed at work on that day.
If the day is a holiday, then this will be set to 0.
Otherwise, this will be the number of employees needed for that weekday (that we entered in Step 2 of data entry in the Settings sheet)
Employees Available: This reflects how many employees are available to work on the day.
If the day is a holiday, this will be set to 0.
If the day is a weekend, this will be set to 0.
It will take into account, the Employees’ hire statuses, Employees’ work schedules on that weekday and vacations planned by employees .
Employees on Vacation: This is how many employees who are employed on that day, but have taken a vacation.
If it is a holiday, this will be set to 0.
If the day is a weekend, this will be set to 0.
Otherwise, this will reflect the count of employees who have planned vacations for that day.
Availability – Needed: This tells us whether we are understaffed or overstaffed or right-staffed.
This is just the difference between Employees Available and Employees Needed. If it is negative, we are understaffed (availability is less than need). If it is positive, we are overstaffed (availability is greater than need). If it is 0, we have the right number of staff (availability equals need).
We will see values in red, there is under-staffing.
We can also create a similar color for over-staffing if needed. We have to edit the conditional formatting rules to do that.
By knowing which days are understaffed or overstaffed, we can take actions accordingly. If we are understaffed, we can either increase capacity by hiring more permanent or temporary employees.
We can also see each employee’s availability on the calendar.
Availability Calendar shows each employees availability and vacation
Green indicates the employee is available to work
Red indicates the employee is on vacation
Purple indicates a holiday
Gray indicates the employee is not working that day (Weekend or the employee does not work on that weekday)
This calendar is set up to handle 50 employees for 366 days.
Step 7: View report to see monthly summary of availability
The Report sheet is also fully automated. It will show the overall summary and summary of availability by month.
Summary Report for Team Vacation Planner – Understaffed Days by Month
We can see, for each month, the number of days in month, holidays, weekends, working days and understaffed days.
% Days Understaffed = (Understaffed Days / Working Days)
This sheet is set up as print-ready. You can print and share with team. You can also export to PDF and share PDF.
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 Google Sheet Template is developed to provide a simple and effective solution to help assess the coverage of team members’ availability during a particular month.
This Google Sheet Template can be used every month to ensure that you are aware of the exact coverage and plan accordingly.
How to Use the Employee Vacation Planner Google Sheets Template
Steps to use this template:
Make a Copy and save this template in your Google Drive.
Configure the input entries.
Dashboard and the Calendar are populated automatically.
Step 1: Make a Copy of the sheet.
Click on the link above and then make a copy of the sheet as shown in the screenshot below.
You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed.
Step 2: Configure the input entries.
This Google Sheet Template is one of the simplest templates to get started. Following are the inputs needed for this template to populate the dashboard and the calendar automatically.
Start Date – The template builds out the calendar for 31 days beginning from the Start Date
# of Employees needed daily – This data is used to identify understaffed days.
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 (Jan 02 to Jan 03 and then on Jan 23), enter them as different entries in 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.
There is a limitation to add 1000 rows of employee vacation that can be captured.
Step 3: Dashboard populated automatically.
Once the inputs are provided, the template automatically calculates the metrics as a quick summary.
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.
The Red bars indicate understaffed days. Understaffed days are days where the number of employees working is less than the number of employees needed.
Step 4: Calendar populated automatically.
This Google Sheet template also has a vacation calendar that visually shows the vacation days on a calendar view. Let’s take a closer look at the top of the calendar.
Each employee is listed on the left. The holidays are shown in orange, weekends in gray and employee vacations are shown in green.
The calendar can display 31 days and 20 employees at a time.
Here is the look of the entire calendar (vertically)
A daily summary metrics is provided at the bottom of the calendar. Number of Employees on Vacation and Number of Employees at work for each day are summarized.
Now that we have the calendar and dashboard automatically updated, we can print or download as PDF. Following is a sample of the document.
In this article, we will explain step by step how to use the Retention Dashboard Excel Template. To learn more about the features of the template and to download, please visit the Product page.
OVERVIEW OF STEPS
Enter data in Employees Data sheet
Refresh Calculations
View/Interact with 3 Dashboards
VIDEO DEMO
STEP 1: ENTER DATA IN EMPLOYEES DATA SHEET
All the input happens in one sheet. This is a table with default 1000 rows.
Employee Data Entry Table
Each row in this table represents one employee’s record.
Start by entering Employee Name, Employee ID and Email information.
Employee ID is a required field.
You can rename EMAIL field and use it to store any other information as needed.
Now, let’s look at 14 other columns in this table. We can categorize them into 3 categories of attributes.
Types of Attributes
4 Employee Attributes – Date of Birth, Hire Date, Gender, Hire Source
Employee Attributes – Date of Birth, Hire Date
These attributes are typically those that do not change over time for an employee
Hire Date and Date of Birth columns should not be repurposed. They are used for calculating Employee Tenure and Age. Modifying this will break the calculations.
Gender and Hire source can be renamed and repurposed. Repurposed means the column can be used to store other information. For example, you can rename Hire Source as ‘Salary Method’ and note values on how the employee gets paid (Check/Cash/Direct Deposit). They will still flow through to the dashboards and be displayed as expected.
These attributes are required to be entered only for employees who have left the company. They can be left blank for active employees.
Exit Date and Exit Type cannot be repurposed as calculations are based on those two columns.
Exit Type requires Voluntary and Involuntary as values.
Also called as Termination Type in some companies.
Exit Reason can be repurposed. It will appear on Exit Dashboard automatically.
Also called as Termination reasons in some companies.
In General values are not limited in any column except Exit Type column.
Exit Type values – Voluntary, Involuntary
When to enter what data?
To begin: When you first start using the template, you will enter all existing employees in your organization.
After that, when new employees join, you will enter that new employee’s information in the table.
When employees move from one department to another or from one location to another, you can just update the specific employee’s data in the table.
When employees leave the company, enter the Exit Attributes for those employees.
How to rename and use customizable fields
In the Employees Data sheet, just click on the column header and rename it.
How to add more attributes
Adding new columns
You can click on cell R3 and type a new column name. This will add a new column. You can add any number of such columns to track information about employees.
Note: new columns will not appear on dashboards automatically.
Step 2: REFRESH CALCULATIONS
The template uses pivot tables and hence the calculations have to be refreshed every time data is added or edited in the Employees Data sheet.
From the DATA ribbon, click on Refresh All.
Refresh Calculations – Data – Refresh All
STEP 3: DASHBOARDS
Once the data is entered and refreshed, we can view the 3 dashboards.
The template provides three automated dashboards:
Retention Dashboard
Retention Dashboard presenting 7 KPIs with trend over the last 12 months.
Employee Retention Dashboard
7 KPIs
Following 7 KPIs are automatically calculated by the template
Active Employees: Number of employees active with the company
Hires: Number of new employees hired
Exits: Number of employees who have left the company
Turnover Rate: (Number of Exits/Avg. Number of active Employees during the period) * 100
Retention Rate: (Number of Exits from Employees active at the Beginning of the period /Number of Employees active at the beginning of the period) * 100
Tenure of employees: Average duration of employment of active employees
Tenure of Exit Employees: Average duration of employment at the time of exit
Snapshot Dashboard
Snapshot Dashboard provides interactive view of # of Employees and Avg. Tenure by 7 different employee and job attributes.
Page 1 shows the number of employees and breakdown by various attributes.
Company Snapshot Dashboard
Page 2 shows the Average Tenure of employees and breakdown by various attributes.
Company Snapshot Dashboard – Tenure
There are 10 Slicers available to drill down.
Dashboard Slicers
To select multiple values in a slicer (for example, Hire Source LinkedIn and Indeed) just press Control while clicking on the specific values.
Retention Dashboard Excel Template – Slicers in Dashboard
Exit Dashboard
Exit Dashboard provides interactive view of exit data. Exit Type (Voluntary/Involuntary) and Exit Reason can be quickly viewed to understand how, why and when employees are leaving the company.
Employee Exit Dashboard
12 slicers are available to drill down or filter.
This dashboard represents only employees who left the company.
Modifying Dashboards
Charts: If you do not need one of the charts in the Dashboard, just remove that one chart by selecting and deleting, without impacting the rest of the dashboard.
Slicers: If you do not need one of the slicers in the Dashboard, just remove that one slicer by selecting and deleting, without impacting the rest of the dashboard.
Renaming Slicers: You can rename the slicers by changing the settings of the slicer.
Right click on a slicer and choose ‘Slicer Settings’
Modifying Slicer Settings
Rename the Caption
Renaming Slicer
Resizing Charts: Depending on the data in your organization, you may need certain charts and slicers to be long or short. Not a problem. Just unprotect the sheet and resize the chart to fit your needs. It will not impact the dashboards in any negative way.
The Dashboards are set up to be ready for print. Press Control+P or use Excel’s file menu to print. You can also use Excel’s default Export to PDF option and export as PDF.
Unprotecting Sheets
You would not need a password to use the template. The template performs the default functionality without having the need to unprotect protected sheets. However, if you would like to modify functionality, you can use indzara as password to unprotect and then make changes.
Whenever you unprotect the sheet, please protect it after making the adjustments.
Extending beyond 1000 employee limit
The template has a hidden sheet named HELP which is limited to 1000 rows. That table needs to be extended down to more rows. Then, automatically the dashboards will pick up more than 1000 rows entered in the Employees Data sheet.
If there are any questions on this template, please post in the comments section below.
Thanks for visiting the support page for Retention Dashboard Excel Template which can help simplify employee retention tracking in your organization.
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.
Input Data is always visible & can be edited easily
Refresh Calculations after entering or updating input data
Backup by saving file regularly
This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.
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 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.
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.
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.
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.
Enter your company’s Weekends and Holidays in WEEKENDS HOLIDAYS Sheet
When employee works different hours (more or less) than the default daily hours, enter them in the HOURS WORKED sheet
When employee takes PTO, enter PTO info in the PTO USED sheet
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
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
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
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
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
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
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
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
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
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
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
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
Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
Rollover Limit: We can set a limit on how many hours are carried over.
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
PROBATIONARY PERIOD
Enter the duration of the Probationary period in days.
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
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
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
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
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
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.
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
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
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.
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.
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
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
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
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
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.
In this article, we cover the common components of a PTO (Paid Time Off) policy for hourly employees. PTO Policy is an important part of a company’s Employee Benefits package. It is one of the factors being considered by candidates when they decide to join a company. An effective employee PTO policy will benefit both employees and employer.
We will go over each of the PTO policy components, explain them in detail and show examples to illustrate how PTO balances are calculated.
COMPONENTS OF A PTO POLICY
Typically, a PTO policy would address the following aspects and they are implemented in the templates from indzara.com.
Probationary Period: Is there a probationary period for an employee where PTO is not accrued?
Accrual Period: How frequently and when does the PTO accrual happen?
Accrual Rate: How much PTO does an employee accrue during each period? Does it change by Tenure?
Rollover Policy: Can balance from one year be rolled over to the next year? If so how much and when?
Max Balance: Is there a maximum balance that an employee can carry at any time?
Now, let’s look at each of them in detail.
1. PROBATIONARY PERIOD
In some companies, employees may not be awarded any PTO for the first X number of days of employment. For example, employee does not earn any PTO during the first 90 days of employment. This period may be called as Probationary period. Work done during this period will not result in any PTO Accrual.
2. PTO Accrual Period
This is to define how often PTO is accrued. Most common options are Weekly, Every 2 Weeks, Twice a Month and Monthly. This is typically aligned with your pay periods.
Weekly: This option means that employee will accrue PTO once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy requirements. For example, accruals happen every Friday.
Every 2 Weeks: Every 2 Weeks policy is similar to weekly except it accrues every other week. For example, every other Friday.
Twice a Month: Accruals can happen 1st and 15th of every month. Or 15th and Last day of every month.
Monthly: For example, we can do accruals on 1st of every month or ‘Last Day’ of every month.
3. PTO Accrual Rate
This defines how much PTO hours are earned by the employee on each accrual day. To explain this, let’s take an example policy: Employee would earn 1 hour of PTO for every 40 hours worked.
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 accrual period.
The employee accrues in multiples of 1 hour.
Another policy example: Employee would earn 4 hours of PTO for every 160 hours worked.
For the first 159 hours of work, the employee does not accrue anything. But once the 160th hour of work is completed, the employee accrues 4 hours of PTO.
The 160 hours are accumulated over periods.
The employee accrues in multiples of 4 hours.
Companies may also have a tiered structure, where loyal employees (with more tenure) will earn PTO at a higher rate. For example,
Employees with <= 2 years of tenure at the company may receive an accrual rate of 1 hour of PTO for every 40 hours worked.
Employees with >2 years of tenure at the company may receive an accrual rate of 2 hours of PTO for every 40 hours worked.
You can have more than 2 tiers as well. This may lead to improved employee loyalty.
4. ANNUAL PTO ROLLOVER POLICY
As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no PTO/vacations taken. Employees are encouraged to take regular time off to maintain a healthy work-life balance.
Also, 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. These are reasons why there is usually a rollover policy.
This policy setting determines how many hours of PTO the employee can carry over from one year to the next year.
Let’s look at three flavors of Rollover policy.
Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
Rollover Limit: We can set a limit on how many hours are carried over. This is the most common practice among companies.
Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year. This is unusual for a company.
ROLLOVER TIMING
With rollover policies, there is another aspect. Some companies may apply rollover at calendar year change (1st Jan of every year for all employees) or on work anniversary dates (which vary for each employee).
5. MAXIMUM ALLOWED PTO BALANCE
The rollover limit only applies to the end of the year balance. Some companies can set an additional limit on maximum balance at any time during the year, to ensure that there is a limit to PTO accrual. This would mean the employees may have to take regular PTO throughout the year, instead of accumulating towards year end.
This is referred to as the Maximum Allowed PTO Balance.
ILLUSTRATION
Now that we defined the key factors, let’s take an example employee and illustrate how these components work and how to calculate PTO policy for an hourly employee.
EMPLOYEE DETAILS
Let’s assume an employee with the following details for this illustration.
Works 8 hrs every weekday
Hire date: 3 July 2019
Probationary Period: 90 Days
Rollover limit: 10 Hours on 1st Jan
Max PTO Balance: 30 Hours
PTO Taken: 1 hour on 25 Oct 2019, 7 May 2020 and 8 May 2020
Accrual Rate: o <= 9 Mths Tenure: 1 Hour PTO per 40 Hours Worked o >9 Mths Tenure: 2 Hours PTO per 40 Hours Worked
PROBATIONARY PERIOD
As the employee was hired on 3rd July 2019, the employee will not be eligible for PTO accrual until Sep 30th.
First day of eligibility will be Oct 1, 2019
Probationary Period
The image above shows the number of hours worked by employee, that are used for calculating PTO accrual.
Though the employee may have worked during the probationary period, the work does not lead to PTO accrual. Only work done from Oct 1st will be used for PTO accrual calculations.
ACCRUAL PERIOD
Since our employee accrues PTO every week, you can see the accrual calendar below where accrual happens on Oct 4, 11, 18, 25…..
First accrual day is Oct 4th.
Weekly Accrual Period in Calendar
The numbers above refer to how much PTO balance changed each period. We will get to how we calculated that shortly.
ACCRUAL RATE
We will begin with simpler calculations and layer in more complexity later.
WEEK 1
Let’s take the first accrual period ending Oct 4th.
There are 2 key inputs needed. Hours Worked and PTO Hours used.
Employee worked for 32 hours from Oct 1st (first eligible date) to Oct 4th (Friday).
Employee didn’t take any PTO.
For every period, we calculate 3 output.
PTO Accrued in Period: How much PTO has employee earned in this period alone?
Unaccrued Work Hours: How much hours did the employee work that have not converted to PTO yet?
PTO Balance at end of period: How much is the PTO Balance at end of period, available for employee to use?
PTO Accrual Rate Calculation – Week 1
PTO Accrued in Period
= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate
= (32+0)* Accrual Rate = 32 Hours *Accrual Rate
= 0 hours of PTO accrued + 32 work hours unaccrued.
PTO Accrued in Period is 0 since employee has not reached 40 work hours yet. However, the 32 hours should count towards the following week’s balance calculation. So, we store that in the ‘Unaccrued Work Hours’ column.
PTO Balance
= Previous Period Balance + PTO Accrued in Period – PTO Used
= 0 + 0 – 0
= 0 hours
PTO Balance for the employee at the end of Oct 4th is 0 as the previous balance is 0 and no PTO was used during the period.
WEEK 2
Let’s move to the next week – ending on Oct 11th.
Inputs:
Employee worked for full 40 hours.
No PTO taken.
PTO Accrual Rate Calculation – Week 2
PTO Accrued in Period
= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate
= (40+32)* Accrual Rate = 72 Hours *Accrual Rate
= 1 hour of PTO accrued + 32 work hours unaccrued.
PTO Accrued in Period is 1 since employee has reached 40 work hours cumulatively. The 32 unaccrued work hours from previous week get added to total hours worked.
PTO Balance
= Previous Period Balance + PTO Accrued in Period – PTO Used
= 0 + 1 – 0
= 1 hour
WEEK 3
Now, third week comes along.
Inputs:
Employee only worked for 8 hours in that week.
No PTO Taken
PTO Accrual Rate Calculation – Week 3
PTO Accrued in Period
= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate
= (8+32)* Accrual Rate = = 40 Hours *Accrual Rate
= 1 hour of PTO accrued + 0 work hours unaccrued.
PTO Balance
= Previous Period Balance + PTO Accrued in Period – PTO Used
= 1 + 1 – 0
= 2hours
WEEK 4
Now, on to the fourth week .
Inputs:
Employee takes 1 hour of PTO and works only 39 hours.
Though the employee does get paid for 1 hour of PTO, for our PTO accrual calculation that 1 hour of PTO will not be used. Essentially, an employee’s PTO time does not earn him more PTO accrual.
Accrual Rate Calculation – Week 4
PTO Accrued in Period
= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate
= (39+0)* Accrual Rate = 39 Hours *Accrual Rate
= 0 hours of PTO accrued + 39 work hours unaccrued.
PTO Balance
= Previous Period Balance + PTO Accrued in Period – PTO Used
= 2 + 0 – 1
= 1hour
WEEK 5
Now for the 5th week.
Inputs: Employee does overtime and works for 41 hours. No PTO Taken.
PTO Accrual Rate Calculation – Summary
PTO Accrued in Period
= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate
= (41+39)* Accrual Rate
= 80 Hours *Accrual Rate
= 2 hours of PTO accrued + 0 work hours unaccrued.
PTO Balance
= Previous Period Balance + PTO Accrued in Period – PTO Used
= 1 + 2 – 0
= 3hours
SUMMARY
In summary,
Employee worked 160 hours in 5 weeks, earned 4 hours of PTO (1 hr per 40 hours worked) and used 1 hour of PTO. The Balance as of Nov 1st is 3 hours of PTO.
Key Takeaways:
PTO Accrual is based on cumulative work hours and not just hours worked in that period.
Unaccrued work hours are tracked to be used for next week’s calculation
PTO used will be deducted from balance
PTO hours do not yield PTO accrual
Formulas:
PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate
PTO Balance = Previous Period Balance + PTO Accrued in Period – PTO Used
ROLLOVER POLICY
Rollover limit was 10 hours and it was applied on calendar year end. So, the first accrual date in Jan 2020 (Jan 3rd, 2020) will see the rollover policy implemented.
The formula we used for calculating balance in the previous section gets an upgrade only for rollover periods.
Assuming balance as of Dec 27th was 11 and 30 unaccrued work hours.
Rollover Policy – Calculation
Calculating the balance for Jan 3.
PTO Accrued
= (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate
= 40 * Accrual Rate
= 1 Hour of PTO Accrued + 0 unaccrued work hours
PTO Balance
= min(Rollover limit, Previous Day Balance) + PTO Accrued in Period – PTO Used = Min (10,11) + 1 – 0 = 10 + 1 – 0
= 11 Hours
Only 10 hours get carried over from Dec 27th Balance due to the rollover limit of 10 hours. If the rollover policy was 0 rollover, then all 11 hours will be lost. In case of unlimited rollover policy, all 11 hours will be carried over.
So, the new balance as of Jan 3rd is 11 hours.
From Jan 10th again, the employee will carry over the balance over to the next week as usual without any rollover limit. The limit is applied only on rollover windows once a year.
TENURE BASED ACCRUAL RATE
As the employee continues to work at the company, he/she reaches the 9 month tenure period where the accrual rate increases to 2 hours of PTO for every 40 hours worked.
Employee completes 9 months on Apr 2nd and on Oct 3rd will be eligible for the new rate.
Tenure Based Accrual Rate calculation
As shown above, the employee earned 1 hour per 40 hours worked on Mar 27.
From Apr 3rd, the employee earns 2 hours of PTO for 40 hours worked.
Note: New rate applies to all hours worked during transition week (Mar 28th to Apr 3) regardless of the exact date when the rate transition happened.
MAX PTO BALANCE
Now, for the last setting: MAX PTO BALANCE of 30 hours.
The employee continues to earn more balance over time and on Apr 24th the balance reaches 30 hours.
MAX PTO Balance calculation
The employee worked 40 hours as usual during the next week.
PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate
= 40 * Accrual Rate = 1 Hour of PTO Accrued + 0 unaccrued work hours
PTO Balance = min (Max PTO Balance , (Previous Day Balance + PTO Accrued in Period – PTO Used))
= Min (30,30+1-0) = Min (30,31) = 30 Hours
Note: Though the PTO accrued should be 1 hour, it is represented as 0 in the image above, in order to avoid confusion. Employee does not actually get the benefit of that 1 hour as it will exceed the Max PTO Balance Policy limit.
When the employee uses 2 hours of PTO during the week ending May 8th, the PTO balance becomes 28 hours.
In the following week, the employee will restart accruing new PTO as 28 is less than 30 (max limit).
CONCLUSION
I hope the above example illustrates how PTO balance can be calculated for hourly employees. The 5 key policy factors we discussed can become complex to implement due to various flavors possible and the calculations are not very easy to build. I spent quite a bit of time writing the formulas to accommodate the various scenarios.
I am happy to inform that you don’t have to spend your time building these formulas. I will be publishing a free Excel Template that comes pre-built with the formulas. You can implement your PTO policy and start calculating balance for an hourly employee within 5 minutes.
Please post your feedback in the comments below. Thank you.
If you are new to Excel templates, please visit the Support Page where there is important information that you should know about using Excel templates.
Overview of Steps
ENTER ONE-TIME SETTINGS
Enter Leave Types (up to 12)
Choose Weekend
Enter Holidays
Enter Reporting Period
Enter Entitlement Settings
ENTER DATA
Enter Employees Data
Enter Leave Entries
VIEW AUTOMATED REPORTS & DASHBOARD
Team Balance Report
Daily Report
Employee Report
Monthly Team Dashboard
Enter One-time Settings
Leave Types
Enter Leave Types in the LEAVE TYPES table.
Enter Leave Types
You can enter up to 12 leave types. You can name the leave types as you need for your organization.
Colors are shown as they will be used in the Reports and Dashboards.
Colors are automatically assigned and cannot be modified.
Choose No for BALANCE TRACKING if you would like the specific leave type to be not tracked in Balance Reports. Examples would be Offsite meetings and training that the employee has to attend but should not be counted as leave against the allowed leave. The employee is unavailable for normal work but is still working for the company in offsite events or training sessions.
Leave the field blank if you would like to track in Balance reports.
Weekends
The template can support customized weekends. You can choose the days of the week that your company considers as weekends. Just select TRUE for those days.
Choose Weekends
When calculating the workdays, the template will exclude weekends.
If an employee is taking leave from one Monday to the next Monday (assuming Sat and Sun are weekends), it will count as 6 leave days and not 8. Sat and Sun will not be counted as leave days against the leave allowed for the employee.
Holidays
The template can support customized holidays. You can choose the days of the week that your company considers as company holidays. Just enter the date and name of the holiday.
Enter Holidays
You can enter as many holidays as needed. When you continue using template for next year, you can just keep adding holidays for next year.
When calculating the workdays, the template will exclude holidays.
If an employee is taking leave from one Monday to Wednesday (assuming Tue is a holiday) it will count as 2 leave days and not 3. Tue will not be counted as leave day against the leave allowed for the employee.
Reporting Period
The reporting period is the range of dates that are used to calculate the leave balance of employees. Just enter the Start and End Dates for your company’s reporting period.
Enter Reporting Period
In the above image, we have set up the reporting period to be from Jan 1, 2019 to Dec 31, 2019.
The max reporting period allowed is 12 months. If you enter a date range that is more than 12 months you will see an error message.
Enter Reporting Period – Error – More than 12 months
Report End Date cannot be before the Report Start Date. An error message will appear if that is detected.
Enter Reporting Period – Error – End Before Start
These validations are present to ensure that the date inputs are correct. If there are errors, the Balance report and Employee report will not be populated.
Entitlement Setup
The template provides two options for entitlement (or quota) setup. You can set the entitlement at the total level or per leave type.
Enter Entitlement Option
If you choose TOTAL, you can then enter the total entitlement of leave days.
Set Total Leave Entitlement
In the above example, we have chosen 15 days as total allowed leave for all employees.
If you choose PER LEAVE TYPE, you can then enter the entitled leave days for each leave type.
Set Leave Entitlement per Leave Type
The leave types that we set to NO for Balance Tracking earlier, do not need any Entitled leave and can be set to 0. In the above example, each employee is allowed 8 days of Vacation, 5 days of Sick, 2 days of Medical and so on.
If you would like to have some employees have more or less entitled leave, you can enter them in Adjustments sheet.
Enter Data
There are two data inputs you would have to enter. 1) Employees 2) Leave Entries
Enter Employee Data
In the EMPLOYEES sheet, you can enter list of employees in your organization.
Enter Employees Data
Employee Name and Start Date are Required Fields. End Date should be used if the employee has left the company.
Employee Name should be unique. Do not repeat the same employee name twice in this table.
Other columns in blue fill can be customized as needed. You can rename the columns and use to store any information you want to store about the employees.
Enter Leave Entries
In the LEAVE sheet, you can enter list of leave entries for all employees in your organization.
Enter Employee Leave Data in Table
For each leave entry, enter the Employee Name, Start of leave, End of leave, Leave Type and Daily Leave Value.
If an employee is taking a single day leave, enter the END DATE same as START DATE. Do not leave the END DATE blank or empty.
DAILY LEAVE VALUE can be partial days as well. 0.5 would represent half a day.
If an employee is taking multiple day leave, for example, from Aug 5, 2019 to Aug 8, 2019 and Daily Leave Value is 0.5, that would mean 4 days of 0.5 each, resulting in 2 whole days of leave taken. Do not enter the total leave value in the DAILY LEAVE VALUE field.
DAILY LEAVE VALUE can be entered as negative value if an employee is working on a holiday or a weekend. Let’s call it a ‘Make up Day’. The template can support such scenarios.
Validations
There are some validations put in place to highlight data entry errors in different colors.
Leave Entries – Validations
If you are entering negative leave value (make up day) it needs to be for a holiday or a weekend.
Leave Start Date cannot be after the Employee has left the company.
Leave Start Date cannot be before the Employee has joined the company.
Leave End Date cannot be before Leave Start Date.
Any leave Entries with these errors will be excluded from reporting.
Custom Columns
Just like in Employees table , we can add custom columns to the Leave table.
Enter Employee Leave Data – Custom columns
You can enter new columns to the end of the table to record information about leave. However, do not insert any columns in between.
That completes our data input for the template. You are now ready to view the automated output from the template.
View Automated Reports & Dashboards
Balance Report
Balance report lists the employees along with their leave balance information at total as well as each leave type.
Team Balance Report
There are 3 controls available in the Balance report to customize it.
Display
You can choose to display one of three measures on the report.
Entitled: Number of days of leave entitled; Used: Number of days of leave used ; Balance: Number of days of leave available as balance
Filter
You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
Please note that any employee who is not employed during the reporting period will automatically not be displayed in this report.
Page #
The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.
Negative balances will be shown in red fill.
Daily Report
Daily report lists all the leave entries for a specific date.
Daily Report
Here, you can type in any date and the report will pull the leave entries for that specific date.
The report also summarize the total leave by leave type on the left.
The last column on display is a custom column where you can replace it with any column from Leave table.
Employee Report
Employee report is a printable report that will display the summary and details of leave and balance for a specific employee.
This report has two pages. Page 1 displays the summary by leave type for the entire reporting period at the top and a calendar view in the bottom showing leave information.
Employee Report Page 1 with Calendar
Page 2 displays the monthly totals by leave type and the summary for the entire reporting period. It also displays the Worked Days calculated as (Total Workable Days in the Month – Leave taken by each employee).
Employee Report Page 2 – Monthly Totals by Leave Type
Monthly Team Dashboard
Team Dashboard displays the leave information of all employees in one specific month.
Page 1 displays the calendar view showing leave of each employee, where leave types are shown in different colors.
Monthly Team Dashboard Page 1 with Calendar view
Page 2 shows the totals for each employee by leave type. It also displays the overall total leave and worked days.
Monthly Team Dashboard Page 2 with totals by Leave Types
There are 3 controls available in the Team Dashboard to customize it.
Custom Column
As the second column next to Employee Name, you can choose any column from Employees table. In the above example, we have chosen Department.
Filter
You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
Please note that any employee who is not employed during the Month chosen will automatically not be displayed in this report.
Page #
The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.
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.
Input Data is always visible & can be edited easily
Backup by saving file regularly
Bonus Tip: Do not insert columns in between in Leave sheet. Add to the end of the table.
This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
How to handle if some employees have different holidays than others?
How to ensure dates are entered in correct format?
What are the differences between the free vacation tracker and the Employee Leave Manager templates?
Which template is suitable for your organization? PTO Manager or Leave Manager?
If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.
