2023 Employee Timesheet – Excel Time card & Work Hours Calculator
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.
31 Comments
What about if we want to cut something from the salary. For example if there is a penalty for something and I need to cut $20. Is there any option to cut that amount without affecting formulas? And by the way, thank you very much for a great job.
Thanks for using our template and sharing your valuable feedback.
This template calculates the hours worked and the wages earned. It does not have any provisions for deductions yet.
We will try to incorporate this feature in our next release.
Best wishes
Great presentation!
It looks like this was meant for HR and not individual employee, correct? Because if the file is going to be kept on a shared drive where I don’t want the salary information to show that others might be able to view.
Do you have a much simple timesheet for an individual but would link to a master timesheet like an admin user?
please advise.
CMP
Thanks for using our template.
This template is to track the time and earnings for one employee.
You can enter your details and earnings.
Best wishes
First of all, Thank you for your great work!!!
It really helps me a lot.
I have a little problem here.
I have a schedule like:
01-Jan-2019
Time In 6:00 PM
Time Out 2:00 AM
02-Jan-2019
Time In 6:00 PM
Time Out 02:00 AM
03-Jan-2019
Time In 6:00 PM
Time Out 02:00 AM
As you mentioned above, I understand it should be separated like
Jan 10 : Starting 18:00 , Ending 24:00
Jan 11 : Starting 00:01, Ending 2:00
However, I can’t add next day schedule and the next one (ex: Jan 11 : Starting 18:00, Ending 24:00)
Since “DATA ENTRY” sheet doesn’t allow me to add same date entry.
If you could, can you tell me how I can solve this?
Thank you.
Thanks for using the template. I have to figure out a way to incorporate this. Sorry, it is not supported yet in this template.
Best wishes.
Thank you for your reply. Still, It is a great template !!! Thank you
Great great template!
As for the problem above I would suppose it is a very common one.
E.g. I have employees working two 4-hour shift per day. Morning and evening.
OR (Greek Restaurant) an employee could work 12:00 – 16:00 and then 20:30-00:30….
So now you need actually 3 separate “time in – time out” columns in order to enter the correct schedule.
How do I incorporate this?
Does it mess too much with the computations if you’d just duplicate columns C and D to add more shifts within a day?
or maybe duplicate the row of the day and ad its part of the shift seperately ?
You are welcome to try. However, I think the formulas already written would not work with that. They would have to be modified.
I will ensure this feature is addressed in the next version.
Thanks & Best wishes.
You are correct. I need to provide more in and out options. Unfortunately, duplicating columns may not work as it is.
I will ensure this feature is addressed in the next version.
Thanks & Best wishes.
I also have viewed and read the article and video. this gave us insight into the easy way to pay employees. but we use several payroll systems. there are those who are paid monthly with overtime hours and there are those per unit of production output. please help input per unit of production output. thank you
Thanks for your suggestion.
We will try to incorporate this in our next release.
Best wishes
i couldnot open the file becouse i have office 2007, plz could u help in that ??
Hello
Our templates are designed for MS EXCEL 2010 or MS EXCEL 2013 or later editions on Windows & MAC platforms respectively.
Best wishes
Hi,
Could you assist for those employee works continuously across two days as following scenario:
22-Dec-2018
Time In 5:41 PM
Time Out 3:05 AM
23-Dec-2018
Time In 6:38 PM
Time Out 11:02 PM
This is addressed in the above post:
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
Please let me know if this does not address your question.
Best wishes.
I have a question, can the monthly report be edited to show a companies specific pay dates/pay period rather then 1st of month and last of month.
For example:
26th Nov – 21st Dec
Thanks for using the template.
The weekly/bi-weekly and monthly reports can be edited. However, please ensure that the totals at the bottom are moved down and the cell formulas copied in the lower rows. Please unprotect the sheet using “indzara” as the password.
Best wishes
HOW ABOUT THE SHIFT WORKER.
THERE ARE SOME START WORK AT 4 PM AND 12AM
Hello
The start and end time of the employee needs to be entered in the data entry sheet.
Best wishes
I have read and viewed both the article and video. Let me thank you for great detail spent on explaining everything. I look forward to using the time sheet. Our billing cycle is twice a month (1st-15th &16th to 31st), is there any way to change either the bi-weekly or monthly time card to cover specific dates?
Hello
Thanks for using the template and sharing a wonderful feedback.
Since most organizations standardize a working week as a unit to calculate pay, this template is designed on a weekly basis. However, a biweekly and monthly reports can be obtained in the subsequent tabs.
Best wishes
This is great article, thanks for sharing, keep up the good work!
You are welcome