How to Create a Timesheet in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Whether you’re a business owner, manager, employer, employee, supervisor, or even a freelancer, there’s always a timesheet involved. 

Learning how to make one in Excel has lots of benefits.

In addition, if you own a business and have employees, you can make them a timesheet of your own design.

Of course, there are lots of templates available online. But if you want to have a deeper understanding of how a timesheet works in Excel, this article is for you.

In this tutorial, we’ll cover how to make a basic timesheet in Excel using different functions and operations. Also, we’ll show you some of the most useful timesheet templates out there so you can either download the one you like or make something similar yourself.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Why use a timesheet?

First off, a timesheet is a medium paper, software, or a spreadsheet — where employees can enter the time they started and stopped working.

The most popular and most common use of a timesheet is in billing and payroll. But that’s not the end of it.

Here are 3 advantages of a timesheet:

1. Cost reduction

If you have a business where employees use a timesheet, you’d be able to see who among your employees are working on time and those who are not.

Depending on the work and billing structure, you’d always know who among them are working poorly.

2. Improve time management

If you know where your time goes during what time, you’d be able to assess and improve how you manage your time. You will have a clear image of how you spend your time.

For employers, this is also a good way to check each employee’s productivity.

3. Accurate billing

This is especially true for contractors like freelancers. If you’re on an hourly pay, a timesheet can help explain to your clients where their money goes.

For clients, a timesheet also lets you see if a contractor or an employee is spending a reasonable time on a project. Without one, it’s hard to argue with an employee spending 8 hours for a project that can be finished within 3-4 hours.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

How to make a timesheet in Excel

To make a timesheet in Excel, you have to envision first what information you’d like to know.

Some of the most useful information include:

  • Name
  • Date (most common: first/last day of the week and date range)
  • Department/Project
  • Days and shifts (morning, afternoon, evening)
  • Rate (usually per hour)

For this tutorial, we’ll make a weekly timesheet template for a regular employee working on a day shift. The employee will need to enter the time he went in and out for morning and afternoon (since there’s lunch in-between).

Kasper Langmann, Co-founder of Spreadsheeto

Note that what you’ll be getting by the end of this exercise is a simple Excel timesheet template with formulas.

Here’s how we imagine the (filled-out) finished timesheet to look like:

completely filled out weekly timesheet

First off, let’s enter the labels:

basic labels for weekly timesheet

The basic operation of our timesheet would need to compute how many hours the employee worked during the day (morning and afternoon).

To accomplish that, subtract the ‘Time out’ to the ‘Time in’ and add the difference for both morning and afternoon.

In words, that would be: (morning time out – time in) + (afternoon time out – time in).

In our sample, let’s add the first row of information so we could test out the formula above:

filled-out first row of timesheet

To check:

  • 12:00 – 7:00 = 5 hours
  • 4:00 – 1:00 = 3 hours
  • 5 + 3 = 8 hours

Awesome, right? We can now apply the same formula to the rest of the rows

For convenience, simply drag the handle on the bottom right of the cell downwards:

copy formula by dragging the handle bar

Now that it’s working, let’s add the ‘SUM’ function to compute the total hours worked during the week.

To do so, let’s use the function’s syntax: =SUM(number1, [number 2], …).

What we want the function to do is add all the total hours per day so we can have the total hours worked for the week.

sum of all hours worked for the week

Feels like smooth sailing? Unfortunately, this is the first part you’ll encounter a bit of a struggle.

The format of the cell which contains the total hours will successfully add all the hours of the week. But the problem arises when the sum reaches 24 hours. Once it does, it will reset to 1.

Why? Because the format is set to a custom ‘Time’ and we all know that a day only lasts 24 hours. 

To illustrate, let’s add all the hours from Tuesday to Friday and see what happens:

incorrect sum of the week's work hours

The employee worked for 5 days straight and the total hours he got from the tracker is only 15 hours. That can’t be right!

To fix this, we have to change the format of the cell.

To do so, click the cell. Then, on the ‘Home’ (default) tab, click the dropdown bar of the number format on the ‘Number’ group.

number format dropdown

Then, click the ‘More Number Formats…’ at the bottom of the dropdown options:

more number formats option on number format dropdown

Then, go to the ‘Time’ category and change the format ‘Type’ to hh:mm:ss as shown below:

time format for timesheet work hours total

We’re now on the final stretch — calculating the total bill.

What we have to do is multiply the total hours worked for the week with the employee’s rate which is $19/hour (in our example).

Here’s the catch:

You can’t directly multiply the total hours by 19. The reason is, the format of the total hours is in ‘Time’.

If you do so, the total bill would just be $30.88.

incorrect total bill

Obviously, 39 multiplied by 19 isn’t 30.88.

The reason for that is Time’ is still a numeric value. However, it’s stored differently. 

The value 39:00:00 is converted to 1.63 or roughly a day and a half. Excel automatically converts 24 hours into 1.

To remedy this, multiply the ‘Time’ value first by 24 before multiplying it by 19. This is how you convert time to decimals in Excel.

As Excel follows the PEMDAS order of operation, place a bracket before and after the values:

correct total bill computation

That’s it! Worked like charm, right?

working weekly timesheet

You can now use the timesheet and start passing it to your employees. 😊

Multiple Employees Template

As promised, we have included 5 awesome templates you can get for free.

The first one is a free Excel timesheet template for multiple employees.

This weekly timesheet template by ClickTime is especially useful for companies and organizations with lots of employees. It’s a simple timesheet where employees can enter their tasks and the number of hours it took for them to complete it.

weekly timesheet template

Daily Timesheet Template

Next in the list is the daily timesheet excel template.

Smartsheet’s daily timesheet template does this by breaking down a day into hourly slots. What the employees have to do is record their activity at the appropriate hour slot.

daily timesheet template

With Break Timesheet Template

Here’s a common timesheet — Excel timesheet formula with a lunch break.

With this timesheet from Microsoft, an employee can enter the time he went out for a lunch break and the time he went back in. It’s a bit similar to the one we made in this tutorial.

complete with break timesheet template

Monthly Timesheet Template

If you noticed, most timesheets deal with hourly and daily activities and time recording.

This one is different. It allows employees to record their daily ins and outs while being able to see the whole month. 

Smartsheet’s Monthly Timesheet Template — Excel makes it possible for employees to enter their daily starting and ending time as well as enter their hourly rate for each type of work, whether it be regular, overtime, or others.

Also, they’ll be able to see their due for each day, week, and month. Convenient, right?

monthly timesheet template

Task/Project Timesheet Template

This is one of the most unique templates you’ll see.

This Project Timesheet Template Excel Format from Template allows employees and contractors to enter the name of the task or project they’re doing and how many hours they’re spending on it on a daily basis.

project-based timesheet template

Wrapping things up…

A timesheet is an important tool both for clients/employers and employees/contractors alike.

If you haven’t found your ideal timesheet from the exercise in this tutorial and the included templates, you can simply make your own in Excel.

Some of the features you like might not be in one template. You might have to combine some of them into one. 😊 But rest assured, you already know most of the functions and operations you’ll need to make a timesheet since they’re included in this tutorial.

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-04T12:25:22+00:00