PTO Balance Calculator (Hourly) – Free Google Sheet Template

Posted on

A Simple Google Sheet template to help you to track the PTO balances of hourly employees. Download this free Google Sheet 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

This Google Sheet Template is designed in such a way that you can Enter the inputs of the PTO policy and are ready to track PTO balance automatically within a few minutes.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the PTO (Paid Time Off) Balance Calculator for Hourly Employee in Excel

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

Features: 

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

How to Use the PTO Calculator Google Sheet template for Hourly Employees

Overview of the Steps

  • Make a copy of the Template
  • Enter the Input Data
  • View Balances in PTO Report 

Step 1: Make a copy of the Template

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Enter the Input Data

  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

Enter Details in the 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.

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.

Step 3: View Balances in 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 Google Sheet template is useful to track Paid Time Off (PTO) for hourly employees in your organization.

You may also be interested in...

PTO Balance Calculator (Salaried) – Free Google Sheet Template

Posted on

A simple Google Sheet Template that can be used as a vacation tracker and PTO vacation accrual calculator. This Google Sheet template can also be used to track and calculate Employee’s PTO (or leave or vacation) accrual balances.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit PTO Balance Calculator (Salaried)

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

Features:

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

How to Use the PTO Calculator Salaried Google Sheet template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. Review the PTO policy and first accrual window details
  4. Enter PTO info
  5. View Balance and Balance Trend

Step 1: Make a Copy

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

Make a copy
Make a copy

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

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

Step 3: Review the PTO policy and first accrual Window details

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

Step 4: Enter PTO info

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

Step 5: View PTO Balance and Balance Trend

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

Components of the 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.

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.

You may also be interested in...

Employee Leave Vacation Tracker – Free Google Sheet Template

Posted on

Whether you are a HR Manager, a Small Business Owner or a Project Manager it is important to track leave/vacation/time-off taken by employees.  This Google Sheet Template is developed to help people in tracking leave/vacation in an organized way, calculating employee attendance.

This Google Sheet Template presents a calendar view of leave across multiple employees and also prints an annual employee report.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Employee Vacation Tracker

Employee Leave Tracker Dashboard for All Employees tracking vacations
Employee Leave Tracker Dashboard for All Employees tracking vacations

Features of the template:

  • Customize settings to meet your business needs
    • Track 5 types of employee leave
    • Customize which days are weekends (not working days)
    • Account company holidays
  • Practical Features
    • Track vacation for multiple employees
    • Employees can have start dates and termination dates
    • Can handle partial days of leave
  • Ease of Use
    • Easy to enter Leave in ranges of dates (example: 4 days of leave from Nov 21st to Nov 24th)
    • Easy to customize colors to suit your preferences
    • Can use the template continuously for many years (keep all your data organized in one file :))
  • Automated Calculations
    • Fully Automated Vacation Tracker Dashboard with monthly calendar view
    • Calculates Worked Days automatically
    • Automated Annual Employee attendance report

How to Use Employee Leave vacation tracker in Google Sheets

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive
  2. Configure the Settings information
  3. Input Employee Data, Leave Information
  4. View reports on Team Dashboard and Employee Report

Step 1: Make a copy of the template

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Configure the Settings information

Enter up to 5 types of leave to suit your company

The template by default supports 5 types of categorizing your leave. Since different companies may have different names and different number of leave types, the template supports customizing them.

Enter 5 types of Employee Leave types to suit your business
Enter 5 types of Employee Leave types to suit your business

By default, you will see the leave types (Vacation, Sick, Unpaid, Half Day, Other). You can just type over these names and replace with your own preferred names.

How to handle half-day leaves

You can see that there is an option to assign ‘Day Value’ to each leave type. This setting is present to handle partial days of leave. For example, I have entered 0.5 for ‘Half Day’ leave type. That means that when an employee takes Half Day leave, template will add 0.5 to leave taken total and reduces the Worked days by 0.5.

If your company allows 2 hours leave in an 8 hour workday setup, that would be 0.25 day. You can create a leave type and enter 0.25 as ‘Day Value’.

Select your company Weekends

In order to calculate attendance of employees automatically, we need to know company weekends so that we can remove them from working days.

I learnt from our readers that weekend days can vary a lot by country. Though Saturday/Sunday is the most common weekend choice, there are so many countries that use a different work week and weekend setup. (Wikipedia article on Weekends)

Select Company Weekends to use for employee attendance tracking
Select Company Weekends to use for employee attendance tracking

Since our goal is to provide a global solution to vacation tracking, we have the option of selecting any combination of weekend days. In the screenshot above, I have chosen Sunday as Weekend.

So, this tells the template to do 3 things.

  1. Template will highlight Sundays visually differently on the vacation dashboard
  2. While calculating attendance, it will not include the Sundays and Saturdays as Working days.
  3. Sundays and Saturdays will not get counted as leave.  When an employees takes Thursday to Monday as Vacation, it will count only as 3 days of leave (Thu, Fri, Mon).

Step 3: Enter Input Details

Enter company holidays

Enter list of holidays in your company.

Enter company holidays
Enter company holidays

The template will treat the holidays similar to Weekends for calculation purposes, but holidays will be displayed in a different color on the calendar. This allows us to view holidays and weekends distinctly.

Enter Employees data

Now, we move to the Employees sheet.

Enter Employee Name, Start Date and Termination Date (optional)
Enter Employee Name, Start Date and Termination Date (optional)

We will enter the Employee Name, Start Date (date when the employee began employment) and then Termination Date (if applicable). The Start Date and Termination Date allow the template to calculate the count of working days for each employee correctly.

Since the template can help you manage leave for multiple employees for long term windows (even many years, as there is no limit 🙂 ), it is quite possible that new employees join the company and current employees may leave. I want the template to work smoothly for you even when that happens.

Enter Employee Leave Data

Now, for the last data entry section, we move to the Leave sheet.

We enter leave taken by each employee here. There are four fields (Employee Name,  Leave Start Date, Leave End Date and Leave Type) in this table.

Enter Employee Leave Data – Employee Name, Leave Start Date, Leave End Date and Leave Type
Enter Employee Leave Data – Employee Name, Leave Start Date, Leave End Date and Leave Type

If an employee takes a 5 day vacation, you can enter that in one row by providing Leave Start and Leave End dates.

If an employee takes a single day leave, please enter same date in Start Date and End Date fields.

Leave Type field has data validation built-in which shows the 5 options we set in the Settings sheet.

Leave Type – Drop down options for data entry
Leave Type – Drop down options for data entry

Similarly the Employee Name also has data validation to allow only employee names available in the Employees sheet. But the drop down option will not activate until a new row is created in the table. If you like typing in the name directly, you can go ahead. If you prefer the drop down options, please read further below in the ‘Extensions’ section where I discuss a work-around .

We are done with data entry. It’s time to see the template’ magic in creating the vacation calendar and report.

Step 4: View Reports

View Team’s Monthly Leave Dashboard

The dashboard is interactive as it allows the user to choose the month for which we want to view the leave data for. We will enter the month by selecting Year and Month.

Choose Month for Vacation Tracker Dashboard
Choose Month for Vacation Tracker Dashboard

There are two pages in this Dashboard sheet. First is the leave calendar view.

Leave Tracker Dashboard – showing calendar for all employees
Leave Tracker Dashboard – showing calendar for all employees

As you can see from the above screenshot, the leave types are shown in different color. The legend is shown at the top indicating what each color means. In addition to the 5 leave types, we also have display for the following: Holidays (purple), Weekends (Dark Gray), Not Employed (Dot pattern) and Not Applicable (Diagonal Line pattern).

Order of priority for colors: Not Applicable > Not Employed > Holidays > Weekends > leave types

Holidays and Weekends are self-explanatory. Not Employed will represent the days before the employee began employment and days after the employee termination date (if applicable).

Not Applicable represents the days that are not actual dates. For example, if you choose November month, 31st day will be Not applicable. For February in a non-leap year, 29, 30, 31 will be Not Applicable days.

You can use this calendar view to assess if you will have enough coverage in your team for specific weeks (for example, during the holiday season). 

Monthly Summary

This sheet also shows the monthly summarized count of days taken off under each leave type, total leave taken and Worked Days.

Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days
Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days

Total Leave is the sum of leave taken under each of the 5 leave types.

If an employee’s leave overlaps with a holiday or a weekend, it does not count as a leave day.

Worked Days is calculated as (Company Working days – Holidays – Weekends – Not Employed Days – Total Leave days)

Worked days represents the number of days an employee worked or is scheduled to work.

You can look at this view during the last week of a month, to calculate the next month’s capacity (in work days) for the entire team. This will be helpful to evaluate whether you have enough capacity to meet the demand (work) ahead in the next month.

View Annual Employee Attendance Report

While the previous sheet was about viewing all employees at once in a calendar view one month at a time, we also need to see one employee’s data across months. This is where the Employee Report comes in handy.

Here we have three inputs.

Select year, Employee name and End date for the Annual employee report
Select year, Employee name and End date for the Annual employee report

The Year and Employee Name selections should be easily understandable.

End Date is set to Today’s date by default. This allows the report to show attendance/leave data as of today.

You can change this End Date by manually typing in any date, if you want to create a report as of any specific date (a month end, for example).

This sheet has 2 pages as well. First, the calendar view.

Employee Attendance – Leave Report showing types of leave, holidays and weekends
Employee Attendance – Leave Report showing types of leave, holidays and weekends

As we discussed with the Team Dashboard, the colors and the legend work the same way. For the chosen employee and year, we can visualize the complete year’s leave patterns.

The second page presents the summarized count of leave and worked days.

Employee Attendance Report – Monthly Summary of leave by type and worked days
Employee Attendance Report – Monthly Summary of leave by type and worked days

You may also be interested in...

Employee Timesheet – Free Google Sheet Template

Posted on

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.

This Google Sheet is developed as a solution for anyone who is looking for calculating working hours effectively. Using this Google Sheet Template create printable weekly timesheet, bi-weekly timesheet and monthly timesheets.

This template is designed for Google Sheets, but if you are looking for an Excel templatePlease visit the following link to download the Excel template : Employee Timesheet Excel Template

How to Use the Employee Timesheet Google Sheet Template

The Template contains the following sheets:

  • Information sheet
  • Settings sheet
  • Data entry sheet
  • Weekly timesheet
  • Bi-weekly timesheet
  • Monthly timesheet
  • Help sheet

We will review each of the sheets and explain the process of calculating work hours in a step-by-step manner. Each workbook can be used to enter only one employee’s details. If you need to enter details of multiple employees then you will have to create a copy of this template for each employee separately. 

You can map an employee’s daily, weekly, bimonthly and monthly work hours by analysing time card entries like time in, time out, break time etc. 

The template is created using various working policies and rules in the industry so it makes a HR’s work easy. The timesheet template automates all calculations so it is easy for the employee/manager/human resource (HR) staff alike to use this timesheet to calculate work hours or pay roll. 

Weekly Timesheet Template -Printable – Excel Template
Weekly Timesheet Template -Printable – Excel Template

Topics

  • What is a Timesheet?
  • Features of the Timesheet Excel template
  • How to use the template?
    • Settings explained
    • Time Card Data Entry
    • Automated Timesheets
    • Print/Export to PDF
  • Common policies and how to implement
  • Frequently Asked Questions (FAQ)

What is a Timesheet?

A timesheet (or time sheet) is a method for recording the amount of a worker’s time spent on each job…The time cards stamped by time clocks can serve as a timesheet or provide the data to fill one. These, too, are now often digital. (Wikipedia,”Timesheet,”n.d., Para 1, https://en.wikipedia.org/wiki/Timesheet)

Timesheets are digital logs  employees’ work hours, travel hours, break hours, overtime and other important information. These data entries record the employee’s productivity and work hours so as to serve useful for payroll calculations.

  • Timesheets are paperless; they save tonnes of money. 
  • Digital timesheets enable streamlined processes for clocking in large amounts of data. 
  • Since, the calculations on timesheets are automated, they are accurate and efficient.
  • We can perform various calculations and display the data in various methods. 
  • We can create multiple reports to map the productivity of employees and even compare their performances. 

Features of the Timesheet Google Sheet template

  • Enables simple data entry: Time In, Time Out and Break duration (in hours and minutes). Here, time is rounded to a minute.
  • Supports 3 Tiers of time and pay rates (Regular, Overtime and Double Overtime)
  • Dates and Days automatically populate based on Start Date. 
  • It is extendable. No need to create new sheets for each week or a month. You can enter a year’s worth of records on the same sheet. 
  • Automated Weekly, Biweekly and Monthly Timesheet reports
  • Customized settings
    • It is designed with formulae that set daily limits, weekly overtime limits
    • The template works based on different overtime rules and policies for weekends, holidays and weekdays. 
    • Supports the California State Rule Policy for Seventh consecutive day of work in a workweek 
    • You can determine the start of the week and the weekends as well. 

Limitations

  • The template is designed for entering data for only one one employee. To track data for multiple employees, please make copies of this template. 
  • It is however important to not keep copies of the sheets in the same file as that will clash with the template’s settings. 
  • Pay rates must be fixed for each employee during the pay period for the calculations to work correctly. 

Overview of the Steps

  • Make a copy of the template
  • Enter Input Data
  • Automated Timesheets

Step1: Make a copy of the template

To use this template simply make a copy of our existing Employee Timesheet Google Sheet Template, rename it as per your needs, and save it on your Google Drive.

Make a Copy of the Sheet
Make a Copy of the Sheet

Step 2: Input Data

The template uses two sheets for data inputs: 

  1. The settings sheet
  2. The data entry sheet (entering Time Card Data)

It uses three sheets for automated data output:

  1. Weekly Timesheets
  2. Bi-weekly Timesheets
  3. Monthly Timesheets

The above-mentioned sheets are print-ready and easy to view.

The Settings Sheet

Employee Info

In the employee info space, we enter details such as Name, Job Title and  Manager of the Employee. We specify a date as to when we want the Timesheet to track the employee’s work hours. Accordingly, the template will automatically populate the dates for the log. There is a custom field for you to enter any other important information about the employee. 

Settings – Enter Employee Information – Timesheet Template
Settings – Enter Employee Information – Timesheet Template

Company Info

Under the company info, we enter the company’s name,  its tagline and address. You can use the custom field here to display any other important information about the company as well. 

Settings – Enter Company Information – Timesheet Template
Settings – Enter Company Information – Timesheet Template

Weekends, Start of Week and Holidays

The template is customizable in the sense that you can select any day(s) of the week to be weekend(s). You can select any day of the week to be the start of the week as well.

The template is customized to be flexible because different countries have different week -beginning days, work days and weekends. 

In the below scenario, we have chosen MON (Monday) to be the start of the week and SUN (Sunday) as the weekend. 

Settings – Select Weekend Days Start Day of Week, Holidays, Weekends
Settings – Select Weekend Days Start Day of Week, Holidays, Weekends

Settings – Choosing Start Day of Week
Settings – Choosing Start Day of Week

Holidays

This template is completely customizable for holidays as well. You can also enter your own endless list of company holidays. A thing to note is that each holiday must be entered separately. 

The template has a feature where it can distinguish between holidays and weekends  automatically and accordingly apply different policies for those. 

Overtime policy settings

Rules and working policies around Overtime and wages vary across countries and companies. The template uses the most common set of rules to calculate the same:

  1. Overtime Tiers and Limits
  2. Policy for Weekends and Holidays
  3. Policy for Seventh Consecutive work day in workweek (California State’s Day of Rest law)

Overtime Tiers & Limits

The template handles 3 work-hours time tiers: Regular, Overtime, Double Overtime. You can calculate pay for work hours based on your company policy by setting up the time tiers. 

The template is setup based on the following policy:  

  • Upto 8 hours of work, the employee will earn a Regular Rate of $X per hour. 
  • After 8 hours and upto 12 hours of work, he or she will earn an Overtime Rate of $Y per hour. 
  • Beyond 12 hours of work, the employee will earn a Double Overtime Rate of $Z per hour.
  • For more than 40 non-overtime hours of work in a week, the employee will earn an Overtime Rate of $Y per hour. This rule is applied when the extra time worked add up to 8 hours only.
  • For more than 40 non-overtime hours of work in a week, if the employee puts in more than 8 hours of work time, it will be calculated as Double Overtime.

General Policy Text

The template generates the policy text automatically so that the company staff can accurately implement it. 

 Please review the below policy to figure out if your company follows the same. Else, you are free to modify the template based on your company policy. 

Employee Pay Calculation – Overtime and Pay Rate – Policy Text
Employee Pay Calculation – Overtime and Pay Rate – Policy Text

Customizable Overtime Tiers and Pay

In the below screenshot, the template is set in the following way: 

  • Upto 8 hours of work, the employee will earn a Regular Rate of $10 per hour. 
  • After 8 hours and upto 12 hours of work, he or she will earn an Overtime Rate of $15 per hour. 
  • Beyond 12 hours of work, the employee will earn a Double Overtime Rate of $20 per hour.

However, the daily work-time limits, weekly work-time limits and rates are completely customizable. If they do not apply, their entries can be left blank. 

Settings – Enter Overtime tiers and Limits for Timecards
Settings – Enter Overtime tiers and Limits for Timecards

Policy for Weekends and Holidays

You can apply any of the Overtime tiers to Weekends and Holidays. Employees can be compensated on either Regular, Overtime or Double Overtime rates for any extra work hours they put in during weekends or holidays. 

Settings – Policy for Weekends and Holidays
Settings – Policy for Weekends and Holidays

Policy for Seventh Consecutive work day in week (California State’s Day of Rest law)

This policy may be applicable only for the state of California. However, if you want to implement such a specific policy for your company’s employees, then you can use this option. Please read the law to understand more: California Law

Settings – Seventh Day policy – Day of rest Law
Settings – Seventh Day policy – Day of rest Law

In the above screenshot, the template has been setup in the following manner:

  • For more than 40 non-overtime hours of work in a week, the employee will earn an Overtime Rate of $15 per hour. This rule is applied when the extra time worked adds up to 8 hours only.
  • For more than 40 non-overtime hours of work in a week, if the employee puts in more than 8 hours of work time, it will be calculated as Double Overtime.

You can customize the overtime tiers and hours limit. The settings are one-time entries and you do not have to change them again unless your company’s policies change.

Settings-Seventh-Day-policy-Day-of-rest-Law-Customize
Settings-Seventh-Day-policy-Day-of-rest-Law-Customize

Just like the other settings, you can opt-out of using this policy by choosing the “No” option. Please refer to the two scenarios below:

Settings- Seventh Day policy – Day of rest Law – Not selected
Settings- Seventh Day policy – Day of rest Law – Not selected

Note: As soon as you choose the “no” option the adjacent work-hour and Overtime entries become redundant whether or not they have values in them.

Settings – Seventh Day policy – Day of rest Law – Not selected
Settings – Seventh Day policy – Day of rest Law – Not selected

Time Card Entry

The daily Time Card entries have the first columns, namely the Date and Day columns, automatically populated through formulas for ease of data entry. Then, Data entry is needed for three columns: Time In, Time Out and Break Time. 

Time In and Time Out refer to the starting and ending time of work for each day, represented in HH:MM format. The entries are made this way:

  • 7:00 for 7:00 AM
  • 19:00 for 7:00 PM (please note that the moment you enter 19:00, the template will display the time as 7:00 PM

Break time is unpaid work and it will be deducted from the actual Work hours. Break time is entered in HH:MM format but it is actually the number of hours and minutes used up.For instance, if an employee has taken 2 hours and 30 minutes of break, the data entry should be 02:30.

Time Card Entry in the Google Sheet template
Time Card Entry in the Google Sheet template

Validation

The template alerts you if you enter a time in the In-Time Column that is greater than your Out-Time. As shown in the below screenshot, the wrong entries will be marked in red to indicate the errors.

Time Card Entry in the Timesheet Google Sheet template – Validation
Time Card Entry in the Timesheet Google Sheet template – Validation

Another thing to note is that, you can not leave just one of the entries (eitherTime In or Time Out) blank. If done, the work hours for the day will be calculated as zero. So, it is essential to review all the entries before calculating payroll. 

Refer to the screenshots below to understand how a blank space in Time Card entry affects the output sheet.

Time Card Entry in the Timesheet Google Sheet template – Validation
Time Card Entry in the Timesheet Google Sheet template – Validation

Time Card Entry in the Timesheet Google Sheet template – Validation - Output Sheet Display
Time Card Entry in the Timesheet Google Sheet template – Validation – Output Sheet Display

Holidays/Weekends

Holidays are shown in blue and Weekends are shown in Yellow.

Time Card Entry in the Timesheet Excel template – Illustrated
Time Card Entry in the Timesheet Excel template – Illustrated

Step 3: Automated Timesheets

The template is automated in such a way that we can update the daily timesheet on sheet instead of using several. And if we need to track data on a weekly, biweekly or monthly basis, the template gives us the reports through three output sheets. 

These reports are print-ready and could be exported to PDF as well: 

  1. Weekly (7 days)
  2. BiWeekly or Fortnightly (14 days)
  3. Monthly (Complete Month)

On Demand

The above-mentioned reports are generated  on demand, that is, if you input the pay period’s start date, the template calculates and pulls up the corresponding work-hours and payroll report automatically.

Auto populating timesheets
Auto populating timesheets

Report Layout

The different components of the  Timesheet report are as follows:

  • The template automatically populates the Employee and Company Information we inputted in the Settings sheet. 
  • The work hours of each day of the pay period are calculated and shown. It also displays the breakdown of Regular, Overtime and Double Overtime hours. If you opt-out of any of the tiers, the break down will exist but the pay will not be calculated for the unselected tier. For example, if you opt-out of Double Overtime, the pay will not be calculated for it. 
  • Weekends are flagged with a W and Holidays with an H.
  • The template displays the total hours worked under each tier, the corresponding hourly rates. Then the Grand Total of hours worked and Pay under each tier is displayed. Finally, the Total Payment to be made for the entire pay period.
  • The template provides Placeholders for employee’s and manager’s signatures.
  • The template displays the Custom fields inputted  in the Settings sheet as well.

Weekly Timesheet – Illustrated
Weekly Timesheet – Illustrated

Weekly Timesheet – Illustrated - without Double Overtime
Weekly Timesheet – Illustrated – without Double Overtime

Custom fields in Timesheets
Custom fields in Timesheets

The following are the 3 screenshots of Weekly, BiWeekly and Monthly Timesheets that can be created with this template.

Weekly Timesheet template

Weekly Timesheet Template -Printable – Google Sheet Template
Weekly Timesheet Template -Printable – Google Sheet Template

Biweekly Timesheet Template

BiWeekly Timesheet Template -Printable – Google Sheet Template
BiWeekly Timesheet Template -Printable – Google Sheet Template

Monthly Timesheet Template

Monthly Timesheet Template -Printable – Google Sheet Template
Monthly Timesheet Template -Printable – Google Sheet Template

Common Policy Settings and How to Implement

The template accommodates various policies and work-rule settings. We will now elaborate on how to implement 6 common policies using this template. 

To illustrate this, we will use this sample Time Card entry of an employee who worked the following hours in a work week:

Employee Work Hours Sample
Employee Work Hours Sample

1. Single Tier policy

In this policy, there is only a single pay rate for all the time the employee works. There is no concept of Overtime here. To set this up, we name the tier and add an hourly rate to it. 

In the image below, we have named this tier “Regular” and pay $10 per hour for the work hours. In this tier, the work done on Weekends and Holidays will compensated with the same hourly rate.

The policy text states that the employee will earn at a Regular rate of $10 per hour. Work done during Weekends will be charged at Regular rate. 

 Process- Print - Timesheet
Process- Print – Timesheet

Output

The employee’s total number of work hours are charged at a Regular hourly rate of $10 and the employee earns $770 for 77 hours of work. Please view the image below to see the calculation.

Single-Tier-Weekly-Timesheet-Example
Single-Tier-Weekly-Timesheet-Example

2. Two Time-tiers Policy with No Weekly Limit

In this policy, we have two-tiers of pay: Regular and Overtime. We have a daily work-hour limit for Regular pay, above which Overtime charges are applied. However, we do not have a weekly work-hours limit in this policy. 

To set this up, we enter hourly rates for Regular  and Overtime work. We also set a daily limit for Regular work. In the below example, we have set up 8 hours of work as Regular time with an hourly wage of $10, above which will be considered Overtime (with hourly wage of $15). 

The policy text reads that the employee will earn at a Regular rate of $10 for the first 8 hours of  a day.The employee will earn at an hourly Overtime rate of $15 for any work done above 8 hours in a day. Work done during weekends will be charged at OVERTIME rate.

Two time tiers settings – with no weekly limit
Two time tiers settings – with no weekly limit

Output

In the below image, we have given an example of how Overtime wages are calculated. The employee earns an Overtime hourly wage of $15 for any work done beyond 8 hours in a day. Also, the employee will earn Overtime pay for 14 hours of work on Sunday as it is a weekend (W). So, in total the employee earns $920 for 77 hours of work.

Two-Tiers-Regular-and-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example
Two-Tiers-Regular-and-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example

3. Two Time-tiers Policy with Weekly Limit

In this policy, we have two Time tiers: Regular and Overtime. We have a daily and weekly limit for the Regular tier as well. 

To set this up, we enter hourly rates for Regular  and Overtime work. We also set a daily and weekly limit for Regular work. In the below example, we have set up 8 hours of work as Regular time with an hourly wage of $10, above which will be considered Overtime (with hourly wage of $15). We have also set up a weekly limit of 40 hours for Regular work, above which will be considered as Overtime. 

Note: If you have a weekly limit but no daily limit, please enter 24 in Daily Limit.

The policy text reads that the employee will earn at a Regular hourly rate of $10 for 8 hours of work in a day. Above which will be considered Overtime with hourly rate of $15. The employee will earn at Overtime for any work done beyond 40 non-overtime (Regular) hours in a week. Work done on weekends will be charged as Overtime. 

Two time tiers settings
Two time tiers settings

Output 

The important difference between this and the previous policy is that when an employee crosses 40 Regular hours of work time in a week, he or she gets compensated at Overtime rates for extra hours worked. 

In the below image given as example, the employee crosses 40 hours of Regular work time by working extra 12 hours on Saturday. The employee puts in 39 hours of work from Monday to Friday and 13 hours of work on Saturday. In this 1 hour is added to 39 hours to make it 40 hours. The additional 12 hours worked on Saturday are counted as Overtime work. Thus, the employee earns $955 for 77 hours of work. 

Two-Tiers-Regular-and-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example
Two-Tiers-Regular-and-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example

4. Three time-tiers Policy with no Weekly Limit

In this policy we have 3 Time tiers: Regular, Overtime and Double Overtime with a daily limit for Regular and Overtime work hours. However, there is no weekly limit for work hours. 

To set this up, we have a daily limit of 8 hours for Regular work compensated at $10 hourly. Above 8 hours upto 12 hours of work is considered Overtime with an hourly rate of $15. Any work done beyond 12 hours in a day is considered as Double Overtime.

The policy text states that the employee will earn a Regular hourly rate of $10 for 8 hours  of work. Beyond which, he or she is compensated at an Overtime hourly rate of $15 which has a cap of 12 hours. Beyond 12 hours, the employee is compensated at a Double Overtime hourly rate of $20. Work done during weekends will be charged at Overtime rate.

Three time tiers settings – with no weekly limits
Three time tiers settings – with no weekly limits

Output

As shown in the below example, the employee gets Overtime charges for work done between 8 and 12 hours in a day. Beyond 12 hours of work, the employee gets compensated with Double Overtime pay charges. Thus, the employee gets Double Overtime charges for three days in the week where more than 12 hours of work was done. In total, the employee earns $935 for the 77 hours of work.

Three-Tiers-Regular-Overtime-Double-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-no-Weekly-Limit-Weekly-Timesheet-Example

5. Three time-tiers Policy with Weekly Limit

In this policy, we have three time tiers: Regular, Overtime and Double Overtime. We have a daily work limit for Regular and Overtime tiers. The main difference between this and the previous policy is that we have set up a weekly limit for Regular work hours.  

To set this up, we have a daily limit of 8 hours for Regular work compensated at $10 hourly. Above 8 hours upto 12 hours of work is considered Overtime with an hourly rate of $15. Any work done beyond 12 hours in a day is considered as Double Overtime. Additionally,  when an employee crosses 40 Regular hours of work time in a week, he or she gets compensated at Overtime rates for extra hours worked.

The policy text states that the employee will earn a Regular hourly rate of $10 for 8 hours  of work. Beyond which, he or she is compensated at an Overtime hourly rate of $15 which has a cap of 12 hours. Beyond 12 hours, the employee is compensated at a Double Overtime hourly rate of $20. Work done during weekends will be charged at Overtime rate. Employees will earn an hourly Overtime rate of $15 for any non-overtime work done in a week that exceeds 40 Regular hours. Work done during weekends will be charged at OVERTIME rate.

Output 

In the below example, the employee earns Overtime charges whenever he crosses 8 work hours and Double Overtime charges when he crosses 12 work hours in a day. 

The employee has completed 39 Regular work hours from Monday to Friday and has worked a total of 13 hours on Saturday. In those 13 work hours, 1 hour is added to the existing 39 hours to complete the 40 work-hour limit.  The next 11 hours are allotted as Overtime while the last 1 hour is allotted as Double Overtime for Saturday. In total, the employee earns $970 for 77 work hours. 

Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-Weekly-Timesheet-Example

6. Three time-tiers with Weekly Limit and Seventh Day Policy

Now, if there were another condition applied to the previous policy, how would it pan out?

Let us assume one more scenario where the employee is given extra pay for the Seventh Consecutive Work day in Week (Day of rest).

To set this up, we choose “yes” in the settings page to enable this policy where on the 7th continuous work day, the employee gets Overtime pay for 8 work hours and beyond that, Double Overtime pay. 

How to set up?

California 7th Day – Day of Rest – Labor Code 550
California 7th Day – Day of Rest – Labor Code 550

Output

In the below example, the employee earns the following:

  1. Regular pay for 8 hours of work each day
  2. Overtime pay for work between 8 and 12 hours each day
  3. Double Overtime pay for work that extends beyond 12 hours each day
  4. Overtime and Double Overtime pay for work that crosses 40 non-Overtime hours in the week.
  5. Here, since we have applied the 7th Consecutive Work Day of the Week Policy. So, on Sunday, instead of applying Weekend work charges,  the template applies the mentioned policy. Since, Sunday becomes the 7th consecutive work day, the employee earns Overtime charges for the first 8 hours and beyond that, Double Overtime charges.

In total, the employee totally earns $1000 for 77 hours of work.

Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-and-California-Law-Weekly-Timesheet-Example
Three-Tiers-Regular-Overtime-Double-Overtime-with-Weekly-Limit-and-California-Law-Weekly-Timesheet-Example

What are some useful resources on this topic?

You may also be interested in...

New Employee Checklist – Free Google Sheet Template

Posted on

This Google Sheet is developed as a solution to document the completion of activities in a checklist. The HR departments in companies can use a copy of this template to track or document the new employee on-boarding activities. Ideally, there is a list of onboarding activities each employee has to complete during the onboarding process. This template can be a solution to track such onboarding activities within your company.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit the New Employee Checklist in Excel

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 date fields)
  • Customize footer with your business information
  • % Complete progress bar
  • All employees’ data in one file

How to Create an Onboarding Employee Checklist in Google Sheets

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive
  2. Configure the Settings information
  3. Input Employee Data
  4. Choose the employee and Print

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.

Make copy of sheet
Make copy of sheet

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

STEP 2: Enter Settings

In the Settings sheet, start by entering the Company name. If we don’t want to print activity dates, then choose No to that.

Finally, enter the list of activities.

Enter Settings
Enter Settings

STEP 3: 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.

Enter Employee Data
These fields can be renamed as needed.

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

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

STEP 4: Print

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

Choosing Employee

The fields in the header can be changed by using the dropdown. This allows us to reorder fields to suit our needs. Also, if you have renamed the employee fields earlier, they will automatically appear here.

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

Activity Status

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

We can customize the footer area by providing contact information for your business and any internal links for reference to the new employees. Feel free to type in your details directly – these fields do not have any formulas.

Footer Details

Now that we have the certificate customized, we can print or download as PDF.  The following are a couple of sample certificates printed.

Sample Checklist

Another Sample Checklist

You may also be interested in...

Employee Vacation Planner – Free Google Sheet Template

Posted on

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. 

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Employee Vacation Planner

 

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

How to Use the Employee Vacation Planner Google Sheets Template

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. 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.

Make a copy

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.

Settings

 

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

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)

Daily Summary

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.

Sample of the document

You may also be interested in...

Recruitment Tracker – Free Google Sheet Template

Posted on

For a simple and effective solution to manage the recruiting process and truly understand its performance via effective Recruiting metrics, use this Google Sheets recruiting 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.

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

Recruitment Tracker Dashboard - Google Sheets- Hiring
Recruitment Tracker Dashboard – Google Sheets- Hiring

HOW TO TRACK RECRUITING IN GOOGLE SHEETS

VIDEO DEMO

Following are the steps:

  1. Copy the template and save in your Google Drive
  2. Enter Settings information
  3. Input candidate or applicant information
  4. View Dashboard
  5. Complete Data Entry after choosing candidate

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.

Google Sheets Template - Make a Copy
Google Sheets Template – Make a Copy

You can save the copy in your own drive for your use. You can then share the copy with your colleagues if needed.

STEP 2: ENTER SETTINGS

In the Settings sheet, start by entering Job Title, Recruiter Name and Job Posted (or Hiring Start) Date.

Settings - Enter Job title, start date and recruiter name
Settings – Enter Job title, start date and recruiter name

The template supports 4 recruitment stages before hiring.

Customize 4 Recruitment Stages
Customize 4 Recruitment Stages

 

You can customize the stages by renaming the default stages.

If you have less than 4 stages, for example 3, please enter only 3 stages as shown below.

Example of 3 Stages in Recruitment pipeline
Example of 3 Stages in Recruitment pipeline

Enter Decline Reasons that are used by your company. These are reasons that you can assign to each candidate that didn’t get selected for the role. Having this information tracked allows understanding the reasons why the candidates are not meeting expectations of the job.

Decline Reasons - 10 values
Decline Reasons – 10 values

STEP 3: ENTER APPLICANTS’ DATA

Enter the list of applications in the DATA sheet.

Enter Application or Candidates Data
Enter Application or Candidates Data
  • When you enter a new Applicant Name, the table will auto-expand by showing gray background color.
  • 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. Please do not edit the formulas.

If you had only 3 stages in your recruitment process, the fourth one will show as below (Hide this column). You can hide the column so that you only see columns relevant to you.

Hide extra column
Hide extra column

STEP 4: VIEW RECRUITMENT DASHBOARD

When hiring is on-going, the Dashboard sheet will display useful information about the recruitment.

Recruitment Tracker Dashboard - Hiring
Recruitment Tracker Dashboard – Hiring

You can see the total number of applications, days since Job Posted Date, number of closed and active applications.

The Recruitment Funnel shows how the applicants flow through the recruitment stages. This is helpful to know where applicants are falling off in the process.

The Decline Reasons show the top reasons why candidates are not selected.

Active Pipeline shows the stages where the open applications are currently in.

STEP 5: DATA ENTRY AFTER CHOOSING CANDIDATE

Once a candidate has been selected, enter the HIRED APPLICANT and HIRED DATE in the SETTINGS sheet.

Hired Date and Applicant
Hired Date and Applicant

The Dashboard will now represent the complete information about this recruitment.

Recruitment Tracker Dashboard - Google Sheets - Hired
Recruitment Tracker Dashboard – Google Sheets – Hired

The status will change to HIRED and the funnel represents all applications (open and closed).

Please note that the Active applications will still represent all applications that have not been closed. So, please close all applications.

Enter Decline reasons for all applications except the hired one.

Complete data entry after hiring
Complete data entry after hiring

Final dashboard should look like this.

Final Recruitment Dashboard
Final Recruitment Dashboard

 

LIMITATION:

The template is designed to be used for tracking recruitment for only one job. If you need to track for a second job, make a copy of the file and use.

Please do not make a copy of the sheet in the same file. It will not work that way.

 

I will soon be working on a version that will support multiple jobs and positions in one file. Please post your interest in the comments below, if that template will be useful.

You may also be interested in...