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.
About 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.
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.
Step 2: Input Data
The template uses two sheets for data inputs:
- The settings sheet
- The data entry sheet (entering Time Card Data)
It uses three sheets for automated data output:
- Weekly Timesheets
- Bi-weekly Timesheets
- 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.
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.
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.
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:
- Overtime Tiers and Limits
- Policy for Weekends and Holidays
- 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.
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.
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.
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
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.
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:
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.
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.
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.
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.
Holidays/Weekends
Holidays are shown in blue and Weekends are shown in Yellow.
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:
- Weekly (7 days)
- BiWeekly or Fortnightly (14 days)
- 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.
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.
The following are the 3 screenshots of Weekly, BiWeekly and Monthly Timesheets that can be created with this template.
Weekly Timesheet template
Biweekly Timesheet Template
Monthly Timesheet 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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
Output
In the below example, the employee earns the following:
- Regular pay for 8 hours of work each day
- Overtime pay for work between 8 and 12 hours each day
- Double Overtime pay for work that extends beyond 12 hours each day
- Overtime and Double Overtime pay for work that crosses 40 non-Overtime hours in the week.
- 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.
What are some useful resources on this topic?
- California State’s FAQ on Overtime policies: https://www.dir.ca.gov/dlse/FAQ_Overtime.htm
- SHRM (Society for Human Resource Management): https://www.shrm.org/ResourcesAndTools/tools-and-samples/policies/Pages/default.aspx
- Overtime Pyramiding: https://simasgovlaw.com/media/blog/overtime-pyramiding-california/