How to Create Timesheets in Excel: A Complete Walkthrough

Do you know what all of your employees are working on at every moment of the day?

The answer is probably no. And while that may seem okay at the moment, this lack of knowledge can have negative effects on your business as your company grows, and as control over day-to-day operations moves away from you.

In this article, you will learn:

• How Excel timesheet templates work
• How to create an Excel timesheet template with formulas
• How to calculate hours worked in Excel
  • Calculating total hours worked
  • Calculating regular work hours
  • Calculating overtime hours
• Calculating employee pay with Excel payroll formulas
• Locking timesheets when using Excel
• Best practices on distributing Excel timesheets
• Make the most of out timesheets
• Automate the whole process

Being unable to account for where your team spends their time can cause your business’ productivity and profitability to take a hit. This can lead to stagnation and decline.

One of the simplest and most effective ways of avoiding this is introducing timesheets to your company. Timesheets keep track of all the hours your team works each day, the tasks they worked on, and when they did them. Read more about benefits of time tracking in this post.

In this post, you’ll learn how to work with timesheets in Excel to record your team’s progress more accurately and improve your productivity.

How Excel timesheet templates work

Timesheet templates created in Excel are incredibly flexible and can be adapted to suit the individual needs that your business has. However, the overall way they work remains the same. Good timesheet practice will follow this framework:

• Set up timesheets globally, individually, or on a team-by-team basis
• Distribute among employees
• Use for all work, individual projects, or only for tracking billable hours
• Submit to team leaders or project managers for timesheet approval
• Collect and collate data to gain an understanding of how the business and teams are functioning as a whole
• Monthly timesheet review sessions looking at the bigger picture to spot inefficiencies and room for improvement

Timesheets work by keeping everyone updated with how long an employee spends at work, and the type of work they do. Passing timesheets up the hierarchy chain means that all levels of the company understand what goes on from top to bottom.

---

---

Creating Excel timesheet templates with formulas

Excel timesheet templates are the simplest way of recording hours, as they require minimal setup time and minimal input from employees.

Let’s take a look at how to use Microsoft Excel for this purpose. We’ve created a video explaining how to create a timesheet in Excel:

Excel allows for several operations within its cells that allow you to create timesheets customized to your team or business. Essentially, just imagine a typical paper time card and its contents, and convert that into digital spreadsheet form.

To start, fill out your sheet with the following information (feel free to modify depending on your company needs):

• Employee information: Name, department, supervisor, rate
• Entry date
• Tasks performed
• Start time and end time
• Regular and overtime hours worked
• Additional notes

You could also customize the sheet by adding colors, or by placing your company logo if you’re planning on printing and archiving it. Your timesheet should look something like this:

The only columns your team will be filling in will be the Date, Tasks, Start Time, End Time, and Notes columns. You can place formulas within the Regular Hours, Overtime Hours, and Total Hours columns that will automatically calculate the hours based on their start and end time entries.

How to calculate hours worked in Excel

Calculating total hours worked

Calculating the daily hours worked by your team with a spreadsheet isn’t difficult. To start things off, set the Total Hours column to count the number of hours between the start time and end time entries. This can be done by inputting the following formula:

• Total Hours = (End Time – Start Time) * 24

It’s necessary to multiply by 24 because Excel registers a whole day as a value of 1 (e.g. 12 hours will result in a value of 0.5).

Calculating regular hours worked

After the Total Hours, you’ll need a formula for your Regular Hours column. This will depend on how many maximum hours your team is allowed to work before it’s considered overtime. Your formula will look like this:

• Regular Hours = IF (Total Hours >= (max regular hours), (max regular hours), Total Hours)

In the example above, 8 is the maximum number of regular hours your team can work before going into overtime. The Regular Hours cell will only accept a maximum value of 8; if the total number of hours goes beyond that, the excess should go to the Overtime Hours cell.

---

Be more productive with automated timesheets

---

Calculating overtime hours

Below is the formula for overtime hours:

• Overtime Hours = IF (Total Hours <= max regular hours, 0, (Total Hours – max regular hours))

Based on the start and end time entries, any hours your team worked beyond the daily maximum will automatically be reflected in the Overtime Hours column. This way, you could easily check for unpermitted overtime work, and calculate their overtime pay with increased accuracy.

Calculating employee pay with Excel payroll formulas

After you’ve set up your timesheets to compute regular and overtime hours, you can use them to calculate your team’s pay. This can be done by multiplying their rates with the total number of both regular and overtime hours.

First, use the following formula to count the total number of regular and overtime hours.

• Total Hours = SUM (first row:last row)

Use the same formula for the Overtime and Total Hours columns. For the amount earned, use the following:

• Pay = hours worked * hourly rate

In the example above, the hourly rate is $10 per hour. Use the same formula for the total number of overtime hours worked, or use a different value for the hourly rate if you have specific overtime policies.

To get the total amount earned, simply add the totals of the regular and overtime hours.

• Total Amount Earned = Regular Amount Earned + Overtime Amount Earned

After that, you’re all set. All your team has to do is put in the time they started and stopped working, and the template will do the rest.

Locking timesheets

After timesheets have been submitted, it’s important to limit access for anyone other than authorized managers. This adds an additional layer of security against timesheet fraud, overpayments, and accidental edits to entries.

Formulas aren’t necessary for locking your timesheets. An easy way to do this would be to have your team upload their timesheets at the end of each week to your company’s shared drive. Or, you could create the timesheets directly there if you’re using a file storage and collaboration platform like Google Drive.

The best way to “lock” timesheets is to have your team upload them to one shared folder. Once all of them are turned in, move them to a separate folder accessible only by you, team leaders, and project managers. This prevents the timesheets from being edited by your team after they are submitted.

Distribute timesheets

Once your Excel timesheet template is prepared, it’s time to implement it across all of your teams. The process is straightforward, but here are some things to keep in mind:

• Customize templates and make adjustments to suit individual projects or variations in employee billing/hourly rates
  • Turn your Excel timesheet into a PDF. PDFs can be customized for each employee, printed off, and handed in each week or month
  • Turn your Excel timesheet into a Word document. Word documents can be managed on a computer and then uploaded to a file-sharing service at the end of the month or another defined period
  • Turn your timesheet into a Google spreadsheet that’s worked on collaboratively. There’s no need to worry about employees doctoring their times or the times of others as Google tracks changes made to the document
• Distribute to employees using one of the methods noted above
• Track the data: Depending on the option used, you can either collate data on a monthly basis and then analyze, or check-in periodically
• Analyze and refine: Make changes to workflows, team structures, and resource distribution based on feedback and data collected by timesheets

One thing to remember is that discipline and consistency are absolutely necessary for timesheets to have maximum effect, but they pay off. After a while, you’ll start to notice your teams’ productivity start rising, and your business’ overall performance improving.

---

---

How to make the most of timesheet templates

The key to making the most out of timesheet templates is following a rigid process, and ensuring that your employees fill them out regularly.

The benefits that timesheet templates have to offer your business (in terms of cost-saving, waste elimination, and data gathering) mean that they are an invaluable business tool that should always be used to the greatest effect. Here are some guidelines to make this happen:

Make employees fill timesheets out honestly, and on a regular basis

Employees should fill out their timesheets multiple times each day, and should make sure that they are completely honest with the time that they input.

One of the benefits of timesheets is that they help to pinpoint inefficiencies and roadblocks that stop your employees from working effectively. You can only identify these when your employees have filled out their timesheets honestly, as any made-up entries will skew the data.

Closely monitor timesheets to spot trends

As a leader within your company, you and other managers should closely monitor the timesheets that employees submit so that you can identify trends (both positive and negative). And, so that you can stay constantly informed of what’s going on in your business.

To do this effectively, spend some time each week looking back on the timesheet information, identifying areas of low (and high) productivity across the board, and then try to match up these areas to events that happened during the week.

Doing this often will help you to continuously improve your business.

Hold regular review sessions with team leaders and managers to deconstruct business’ performance and productivity

To make sure that you are getting the best out of your employees, hold regular sessions to review the performance of individual employees and your business as a whole.

Having regular review sessions means that even if you aren’t heavily involved with what goes on in your company on a day-to-day basis, you’ll still have a great understanding of how things are actually running and that you are positioned to make informed decisions about your company’s future.

Continuously refine your company’s processes and workflows

Having all of the data about how your employees work means that you can make informed decisions that refine workflows and change your company’s processes. Both of these can make your company more cost-efficient and more productive.

Automate the whole process with Hubstaff’s online timesheets

Hubstaff is a timesheet software that allows you to automate timesheets and time management. It tracks all of your team’s work hours, and the tasks they work on, and automatically generates detailed and accurate timesheets.

Instead of entering hours into a timesheet template, Hubstaff tracks time toward specific projects and tasks with a simple start and stop feature available through desktop, web, or mobile apps.

With Hubstaff, your team doesn’t have to note what time they have arrived in the office or started working, or what tasks they spent time on. They can start tracking their time and the task with just the click of a button. Hubstaff will handle everything else from timesheet creation to payroll processing.

Managers can get daily timesheets emailed directly to them, see how much of a budget has been used, set hourly limits for team members, and review and approve timesheets all from one central dashboard.

Compared to timesheet templates, Hubstaff offers a higher level of accuracy because you can turn on features including app and URL tracking, random screen capture while the timer is running, activity rates, and more. With Hubstaff, there’s no question about what was worked on when.

Finally, you can use the hours tracked and pay rates to automatically issue payroll to your team. The same goes for client invoices; just review timesheets at the end of each pay period, set bill rates, and you can send invoices much sooner.

Are you ready to start using timesheets?

Timesheets, when used correctly, are hugely powerful tools that can help to transform your business for the better.

They can dramatically increase productivity, make it easier for your employees to motivate themselves, and make your business run more smoothly.

To get started implementing timesheets into your business, you can download timesheet templates that we prepared, or create one specifically tailored for your business with a spreadsheet app and our guide above.

Alternatively, you can use Hubstaff to automate the whole process, and not worry about forgetting to note what time you clocked in or out.

Whatever your choice is, we’d like to know if you use timesheet templates in your organization, and how it has contributed to your team’s success. Do you have any go-to templates you’ve been using for years, or software tools that might be helpful to other business owners as well? Let us hear about it in the comments below.

This post was originally published September 15, 2016, and updated August 2019.

---