How to Create a Timesheet in Excel - Template Included

How to Create a Timesheet in Excel

In this video, we’re going to show you how to create your own Excel timesheet. We’ll cover all the formulas you need to set this up properly, so your team can start logging their work hours. Plus, we have the template that was used in this video available for you to download.

hubstaff.com/resources/free-timesheet-templates

Now, there are three key columns we’ll focus on in our Excel timesheet:

Total hours (0:49)
Regular hours (1:25)
Overtime hours (2:24)

Those three columns will contain the more complex formulas used in the timesheet. Starting with total hours, our formula looks like this:

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

This formula is pretty straight forward. The only thing that might seem odd is the fact that we’re multiplying by 24. Which we have to do because excel interprets an entire day or 24 hours as a value of 1. For example, 12 hours would result in a value of 0.5.

Regular Hours = *I can't add angle brackets in the description for this equation but it's shown on screen at (1:25)*

Next, we have regular hours. This formula is a little longer. We’re going to be placing a limit or a cap in the regular hour's column. We’re assuming, for this example, that 8 hours is the max amount of time someone can work before accumulating overtime.

Overtime Hours = *I can't add angle brackets in the description for this equation but it's shown on screen at (2:24)*

Finally, we have overtime hours. In this cell, we’re adding a formula that’s going to take any excess hours logged in the regular hour's column and record them as overtime. I gave the example of working 10 hours a day. The regular hour's column maintains the limit of 8 regular hours in a day, but the overtime column now has 2 hours recorded.

Blog Post - bit.ly/303PqhT

SUBSCRIBE TO THE CHANNEL - bit.ly/2YwfBwZ

Connect with us!
Hubstaff blog - blog.hubstaff.com/
Facebook - facebook.com/hubstaffapps/
Twitter - twitter.com/Hubstaff