If you are looking for a simple and effective Timesheet template or Time Card template, you have reached the right place. In this blog post, I will present a free time sheet template in Excel and explain step by step how we can use it to calculate hours worked.
You can enter time card entries like time in, time out and break time and let the template create printable weekly timesheet, biweekly timesheet and monthly timesheets.
If you are an employee, no more wondering ‘How many hours did I work?’ or searching for ‘How to calculate hours worked?’. This timesheet template makes it easy by automating all the calculations.
If you are an HR (Human Resources) professional who needs a work hours calculator for the employees in the company or team, you can use this too. The template accounts for various commonly used rules in the industry and makes your task simple and easy.
Essentially, this template can be used by anyone who needs a timesheet in Excel or work hours tracker or payroll hours calculator.
This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Employee Timesheet in Google Sheets.
What is a Timesheet?
Timesheet is a very commonly used tool in tracking number of hours worked by an employee during a pay period.
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.
Benefits of a timesheet in Excel are obvious.
- We can save paper and can print only when needed.
- We can improve accuracy by automating the calculations.
- We can easily make copies as needed and save time.
- We can perform calculations and create reports on multiple timesheets.
Features of the template
- Simple Input: Time In, Time Out and Break duration (minutes)
- Supports up to 3 Tiers of time and pay rates (Example: Regular, Overtime and Double Overtime)
- Customize settings
- Accommodates daily limits and weekly overtime limits
- Accommodates different overtime rules and policies for weekends and holidays (compared to weekdays)
- Supports Policy for Seventh consecutive day of work in a workweek (California State rule)
- Week can start any day
- Automated Weekly, Biweekly and Monthly Timesheet reports
- Dates automatically populate based on Start Date
- Can be used continuously forever. No need to create a new sheet for each week or month.
- Time is rounded to a minute
Limitations
- Designed for one employee per file. To track for multiple employees, please copy the file and use. Please do not copy the sheets in the same file.
- Pay rate must be fixed for employee during the pay period.
Requirements
- Microsoft Excel for Windows (2010 & above)
- Microsoft Excel for Mac (2013 & above)
For a template that supports multiple employees, please see Timesheets Manager template.
Free Downloads
Video Demo
How to create timesheets in Excel?
The template uses two main sheets of data entry input and three automated output sheets.
Let us split the steps into three phases.
- Entering the Settings
- Entering Time Card data
- Viewing and Printing Timesheet reports
Settings
1. Employee Info
We begin by entering Employee details such as Name & Job Title of the employee and Name of the Manager of the Employee.
We also enter a Start Date. This is the date from when we want to track time worked by employee. The template will use the date to auto-populate the dates in the Time card.
A Custom Field is provided to allow the user to type any information about the employee. This value will be displayed on the timesheet automatically.
Next, we enter information about the employer (or company).
2.Company Info
We can enter the name, a tagline and Address of the company.
A Custom Field is provided for Company information as well.
3. Weekends, Start of Week and Holidays
Weekends
The template is designed to fit various scenarios when it comes to Weekends. We can choose which days are weekends. In the above image, we have selected SUN (Sundays) as weekends.
Start of Week
We can choose the starting day of a Week. We understand that different companies and different countries may have different weekend days and week beginning days. The template can handle all of them.
Holidays
We can enter the list of holidays in the holidays table.
- Each date must be entered separately.
- There is no limit to the number of holidays.
The template has a feature by which it can automatically apply different policy for weekends and holidays.
Overtime policy settings
Now, we are entering the more fun part.
Rules and policies around overtime or multiple tiers of pay could vary across companies and countries. We have tried to capture most common set of rules in this template.
There are three components to this.
- 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 Excel template can handle up to 3 time-tiers (for example, Regular, Overtime and Double Overtime).
You can modify these inputs to implement several different policies. We will cover many scenarios later in this blog post in detail.
In the screenshot above, we have Regular, Overtime and Double Overtime. The setting chosen above implies the following policy.
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 to up to 12 hours in a day. Employee will earn at DOUBLE OVERTIME rate of 20 for any work beyond 12 hours in a day.
Employee will earn at OVERTIME rate of 15 for hours worked after 40 non-overtime hours in a week.
Policy for Weekends and Holidays
You can apply any of the tiers to Weekends and Holidays.
If we choose ‘Double Overtime’, then all the work done during holidays and weekends will be assigned to ‘Double Overtime’.
To ensure that the policy is correctly implemented, the template also automatically generates this policy text.
Please review and ensure that it accurately reflects your company policy.
Policy for Seventh Consecutive work day in week (California State’s Day of Rest law)
This policy may be applicable only for state of California. However, if you need to implement a specific policy for seventh consecutive work day in a week, then you can use this option.
California Law
Just like everything else in the template, it is optional and allows customization.
You can just choose No if this does not apply to your business.
You can also customize the hours limit and the tier that needs to be applied.
The Settings discussed so far are to be entered one-time and we don’t have to change them unless your company’s policies change.
Time Card Entry
Now, we are ready to enter the daily time card entries.
The first two columns are calculated with formulas. The dates are automatically calculated to help you with data entry.
Data entry is only needed for the three time columns. Time In , Time Out and Break time.
They are straight forward just like their names indicate. Enter the starting and ending times of work each day.
We will enter in HH:MM format. Enter just 8:00 for 8:00 AM and 17:30 for 5:30 PM.
Break time is unpaid work and the number of hours entered will be deducted from Work Hours.
We enter break time in HH:MM format but this time it is number of hours and minutes of break. For example, enter 00:30 for 30 minutes of break or 01:30 for 1hr 30 mins of break.
Validation
We cannot have Time Out before Time In. If you have an entry that violated this rule, you will see the red borders as shown below, to indicate that there is an error.
Holidays/Weekends
Holidays are shown in blue and Weekends are shown in Yellow.
Automated Timesheets
With this template, we don’t need a separate Weekly Timesheet template, BiWeekly Timesheet template and a Monthly Timesheet template. They are all combined into this one template. The template has 3 in-built timesheet reports that can be printed on-demand.
- Weekly (7 days)
- BiWeekly or Fortnightly (14 days)
- Monthly (Complete Month)
All the three timesheets are printer-friendly and can be printed or exported to PDF and saved.
On Demand
They are on-demand, in the sense that you enter the pay period start date and it pulls the corresponding data automatically.
Now, let’s look at the layout and the different components.
- Employee Information and Company Information we entered in the Settings sheet carries over here automatically.
- The work hours for each day in the pay period are shown with the breakdown of Regular vs Overtime vs Double Overtime hours. If you have only two time-tiers in your settings, then only two will appear. It is designed to work seamlessly.
- Weekends will be flagged with a W and Holidays with an H.
- Hourly rates are displayed. Total Hours under each time are calculated. Grand Total Hours and Pay are calculated for the entire pay period.
- Placeholders for signature of employee and manager are provided.
Custom fields provided in Settings sheet will appear on the Timesheet as well.
The following are the 3 screenshots of the 3 timesheets that you can create with this template.
Weekly Timesheet template
Biweekly Timesheet Template
Monthly Timesheet Template
Print / Export as PDF
It is recommended that you print and save a hard copy or save as PDF and store, depending on your company’s document retention policies.
Export to PDF
Common Policy Settings and how to implement
With all the above-mentioned settings and options, the template supports numerous combinations of policies that can be implemented easily. Now, let us choose 6 common policies and see how we can implement them in the template.
To illustrate this, we will use the employee who worked the following hours in a work week.
Single Tier policy
This is straight forward. All the time worked are given same pay rate. There is no overtime policy as such.
How to set up?
We just enter the name of the tier and the hourly rate. In the image above, we have entered $10.00 per hour for Regular time.
Since there is only one tier, the Weekends will be charged at the same Regular rate.
Policy Text
Employee will earn at REGULAR rate of 10.
Work done during weekends will be charged at REGULAR rate.
Result
All the hours worked get labelled as Regular time at $10 per hour and the employee gets paid $770 for the 77 hours of work.
Two time-tiers with no Weekly Limit
In this scenario, we have Regular and Overtime pay. We have a daily limit for Regular time.
How to set up?
Policy Text
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 hours in a day.
Work done during weekends will be charged at OVERTIME rate.
Result
This time, the employee earns for Overtime for any hours worked beyond 8 hrs in a day. Also, Sun being a weekend (marked with a W) all the 14 hours worked on that day counts as Overtime.
The employee gets paid $920 for the 77 hours of work.
Two time-tiers with Weekly Limit
In this scenario, we have Regular and Overtime pay. We also have daily limit and weekly limit for Regular time.
How to set up?
If you have a weekly limit but no daily limit, please enter 24 in Daily Limit.
Policy Text
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 hours in a day.
Employee will earn at OVERTIME rate of 15 for hours worked after 40 non-overtime hours in a week.
Work done during weekends will be charged at OVERTIME rate.
Result
The main difference between this and the previous use case is that the employee crosses 40 hours of Regular work time for this week on Saturday.
After completing 39 Regular hours from Mon to Fri, the employee works 13 hours on Saturday. 1 hour of this 13 hours will be counted as Regular time, while the remaining 12 hours will be Overtime.
The employee gets paid $955 for the 77 hours of work.
Three time-tiers with no Weekly Limit
In this scenario, we have Regular, Overtime and Double Overtime pay. We have a daily limit for Regular time and Overtime.
How to set up?
Policy Text
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 to up to 12 hours in a day. Employee will earn at DOUBLE OVERTIME rate of 20 for any work beyond 12 hours in a day.
Work done during weekends will be charged at OVERTIME rate.
Result
Here, the employee gets Double Overtime pay for any hours worked beyond 12 hrs in a day. Hence, for the three days when the employee worked 13 hrs each, that 13th hour is assigned as Double Overtime.
The employee gets paid $935 for the 77 hours of work.
Three time-tiers with Weekly Limit
In this scenario, we have Regular, Overtime and Double Overtime pay. We have a daily limit for Regular time and Overtime. We also have a weekly limit for Regular time.
How to set up?
Policy Text
Employee will earn at REGULAR rate of 10 for the first 8 hours in a day. Employee will earn at OVERTIME rate of 15 for hours worked after 8 to up to 12 hours in a day.
Employee will earn at DOUBLE OVERTIME rate of 20 for any work beyond 12 hours in a day.
Employee will earn at OVERTIME rate of 15 for hours worked after 40 non-overtime hours in a week.
Work done during weekends will be charged at OVERTIME rate.
Result
The employee crosses 40 hours of Regular work time for this week on Saturday. After completing 39 Regular hours from Mon to Fri, the employee works 13 hours on Saturday.
Only 1 hour of this 13 hours will be counted as Regular time, next 11 hours will count as Overtime while the last 1 hour will be Double Overtime. Since the weekly limit for Regular hours is 40, the template will ensure that anything more than 40 will not be counted as Regular.
The employee gets paid $970 for 77 hours of work.
Three time-tiers with Weekly Limit and Seventh Day Policy
Let us do one more scenario where the Seventh Consecutive Work day in Week (Day of rest) policy applies.
How to set up?
Result:
Now, the employee earns more on the 7th consecutive day of work in the week. On Sunday, employee earns first 8 hours as Overtime and the remaining 6 as Double Overtime.
The employee totally earns $1000 for 77 hours of work.
Frequently Asked Questions (FAQ)
How many days of data entry can we do?
The timesheet template is set up to record 366 days of timecard entries. However, you can easily extend it to any number of days or months or years by simply clicking on the end of the table and dragging down.
Can the names be other than ‘Regular’, ‘Overtime’ or ‘Double Overtime’?
Yes, The names of these time tiers can be changed as you need in the Settings sheet.
Some sheets are locked. How to unprotect and edit?
The sheets are locked/protected to prevent unintentional editing of formulas. If formulas are edited, the functionality and the accuracy may be impacted.
If you really need to do it, please unprotect the sheet.
Enter indzara as password.
After making modifications, protect again.
How to change the currency?
You can select the cells where there are US$. Press Ctrl+1 to open the Format Cells dialog box.
In the Number tab, select the currency symbol you need and press OK.
How to change the Date formats used?
We have used DD-MMM-YYYY format. You can change it as needed.
Select the cells you need to change. Press Ctrl+1.
What if employee works continuously across two days?
If an employee works across days, then we need to enter in 2 separate rows.
For example, if an employee starts work on 25th June at 8 PM and finishes work on 26th June at 4 AM, then we enter
- June 25th: Starting 20:00 , Ending 24:00
- June 26th: Starting 00:01, Ending 4:00
However, if the employee is working a shift on June 26th also, this template does not support that scenario. The premium template does support that. You can enter overnight shifts easily. Timesheets Manager template.
What if we want tiers to apply to weekends and holidays?
Currently, the weekends and holidays will use just one chosen time (example: Overtime) and there are no tiers. Workdays can have tiers. If you do not want weekends and holidays to be treated differently compared to workdays, then you can just not enter any holidays and not choose any days as weekends.
If multiple rules apply, which one trumps?
Among the 3 components (Overtime tiers, Holidays/Weekends and Seventh Consecutive workday), the following is the order of implementation. If more than one rule was applicable, the higher placed rule is implemented.
- If it is the seventh consecutive workday in a workweek, that policy is implemented if chosen.
- If it is a holiday or a weekend, the time tier chosen for that is implemented. No multiple tiers.
- Otherwise, the multiple tiers policy is implemented.
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/
Can we see the formulas used?
There are several hidden fields which calculate all the hours worked and pay. These are hidden to avoid any clutter in the data entry sheet and to prevent accidental editing of formulas.
However, you are welcome to view them if you would like.
First, unprotect the sheet as explained earlier.
Select columns E and P.
Right click and choose Unhide.
You can view all the calculated fields now.
Please hide them again if you do not need to view them. Please do not delete any columns from the spreadsheet.
Can we add our company logo to the Timesheets?
Yes, of course.
You can insert any image from the Insert menu. Insert —> Pictures. Choose any picture.
Then, resize the picture if needed and move it to where you need.
Recommended Template
For tracking multiple employees’ time in one file, please try the Timesheets Manager template.
Related Free Templates
I hope this template will be useful in your attempts to easily track work hours.