Employee Timesheet Template – Excel Time card & Work Hours Calculator

Posted on
Weekly Timesheet Template -Printable - Excel Template

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.

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

Topics

  • What is a Timesheet?
  • Features of the Timesheet Excel template
  • Free Excel Template Download
  • Video Demo
  • 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?

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. 

https://en.wikipedia.org/wiki/Timesheet  

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)

Free Excel Template Download

Employee Timesheets Template

Employee Timesheets Template with sample data

 

Video Demo

On YouTube Channel

How to use the template

The template uses two main sheets of data entry input and three automated output sheets.

Let us split the steps into three phases.

  1. Entering the Settings
  2. Entering Time Card data
  3. 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.

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

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

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

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

Settings - Select Weekend Days Start Day of Week and Holidays
Settings – Select Weekend Days Start Day 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

Settings - Choosing Start Day of Week
Settings – Choosing Start Day 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.

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

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

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.

Settings - Policy for Weekends and Holidays
Settings – Policy for 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.

Employee Pay Calculation - Overtime and Pay Rate - Policy Text
Employee Pay Calculation – Overtime and Pay Rate – 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

Settings - Seventh Day policy - Day of rest Law
Settings – Seventh Day policy – Day of rest 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.

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

You can also customize the hours limit and the tier that needs to be applied.

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

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.

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

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.

Time Card Entry in the Timesheet Excel template - Validation
Time Card Entry in the Timesheet Excel template – Validation

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

 

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.

  1. Weekly (7 days)
  2. BiWeekly or Fortnightly (14 days)
  3. 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.

On-demand timesheets ready to be printed
On-demand timesheets ready to be printed

 

Now, let’s look at the layout and the different components.

Weekly Timesheet - Illustrated
Weekly Timesheet – Illustrated

 

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

Custom fields in Timesheets
Custom fields in Timesheets

 

The following are the 3 screenshots of the 3 timesheets that you can create with this template.

Weekly Timesheet template

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

 

Biweekly Timesheet Template

BiWeekly Timesheet Template -Printable - Excel Template
BiWeekly Timesheet Template -Printable – Excel Template

 

Monthly Timesheet Template

Monthly Timesheet Template -Printable - Excel Template
Monthly Timesheet Template -Printable – Excel 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.

Print Employee Timesheet from Excel
Print Employee Timesheet from Excel

 

Export to PDF

Export Timesheet as PDF
Export Timesheet as 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.

Employee Work Hours Sample
Employee Work Hours Sample

 

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

Single Time-Tier - settings
Single Time-Tier – settings

Policy Text

Employee will earn at REGULAR rate of 10.

Work done during weekends will be charged at REGULAR rate.

Result

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

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.

 

2. 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?

Two time tiers settings - with no weekly limit
Two time tiers settings – with no weekly 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.
Work done during weekends will be charged at OVERTIME rate.

Result

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

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.

 

3. 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?

Two time tiers settings
Two time tiers settings

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

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

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.

4. 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?

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

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

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

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.

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

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

 

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.

6. 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?

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

Result:

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

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.

Extending timesheets for more days
Extending timesheets for more days

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.

Unprotect sheet
Unprotect sheet

Enter indzara as password.

 

Enter password to unprotect
Enter password to unprotect

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.

Change Currency in Timesheet
Change Currency in Timesheet

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.

Change date format used in Timesheets
Change date format used in Timesheets

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

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.

  1. If it is the seventh consecutive workday in a workweek, that policy is implemented if chosen.
  2. If it is a holiday or a weekend, the time tier chosen for that is implemented. No multiple tiers.
  3. Otherwise, the multiple tiers policy is implemented.

What are some useful resources on this topic?

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.

 

Unhiding hidden calculated fields
Unhiding hidden calculated fields

Select columns E and P.

Right click and choose Unhide.

Excel formulas to calculate timesheets
Excel formulas to calculate timesheets

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.

Inserting Company Logo as Picture
Inserting Company Logo as Picture

 

Then, resize the picture if needed and move it to where you need.

Timesheet with Company Logo
Timesheet with Company Logo

 


Recommended Read

More Excel Templates for HR

 

 

I hope this template will be useful in your attempts to easily track work hours.

Please post your feedback in the Comments section below. Please share this page with your friends if you find it useful.

23 thoughts on “Employee Timesheet Template – Excel Time card & Work Hours Calculator

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

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

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

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

      1. Thank you for your reply. Still, It is a great template !!! Thank you

      2. 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?

        1. or maybe duplicate the row of the day and ad its part of the shift seperately ?

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

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

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

    1. Thanks for your suggestion.

      We will try to incorporate this in our next release.

      Best wishes

  4. i couldnot open the file becouse i have office 2007, plz could u help in that ??

    1. Hello

      Our templates are designed for MS EXCEL 2010 or MS EXCEL 2013 or later editions on Windows & MAC platforms respectively.

      Best wishes

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

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

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

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

  7. HOW ABOUT THE SHIFT WORKER.
    THERE ARE SOME START WORK AT 4 PM AND 12AM

    1. Hello

      The start and end time of the employee needs to be entered in the data entry sheet.

      Best wishes

  8. 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?

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

  9. This is great article, thanks for sharing, keep up the good work!

    1. You are welcome

Leave a Reply

Your email address will not be published. Required fields are marked *