Accurately tracking the amount of time your employees work every day is a critical task of a business owner. While there are numerous strategies employers can use to record the time their employees work, one effective and low-cost approach is to use an Excel timesheet.
What is a timesheet?
Timesheets are virtual or physical documentations where work time is recorded. They allow you to quickly see how many hours an employee has worked during the week or review longer periods of time to identify work and productivity patterns.
The original format for time tracking was a handwritten ledger. While this was sufficient for a business owner or manager who saw each employee as they walked in and out of the factory or workplace every day, modern businesses need a more automated, flexible solution. Excel timesheets allow for the same level of autonomy and customization that an old-fashioned ledger once did, with the added functionality of automatic computations found in time-tracking software.
Digital timesheets make payroll easier, but they also make project management more efficient. Depending on the type of timesheet you use, you can refine the tracking process by project to get a more accurate overview of the hours employees spend on individual projects. Doing so also informs ROI considerations and project budgeting.
How do you make a timesheet in Excel?
Learning how to make a timesheet in Excel may seem daunting at first. It requires precision and an understanding of how Excel formulas work. However, once it's set up, you can duplicate and continue using the same template with the Excel timesheet formula you have created.
Making a basic Excel timesheet that can be printed and tracked by hand is one of the easiest ways to start. The first step in creating your Excel timesheet is identifying the categories pertinent to your calculation needs. Popular timesheet categories include:
- Breaks
- Clock-in time
- Clock-out time
- Date
- Name
- Job
- Lunch
- Shift
- Total number of hours worked
- Total number of holiday hours earned/worked
- Total number of sick hours earned/worked
- Total number of vacation hours earned/worked
Next, you need to format the Excel timesheet so it has all the categories you need to begin tracking your employees' time.
You will also need to include the name of the company and employee, as well as signature lines for the supervisor and employee to verify the data before it is sent to payroll. Where these items are located is an aesthetic preference; typically, the name of the company (and a space for the name of the employee) are located at the top of the sheet, while the signature lines are located at the bottom.
Once the timesheet is saved, you can print as many copies as you need.
How do you calculate hours worked in Excel?
Calculating hours worked requires the right Excel formula for your timesheet needs. Adding formulas to an Excel sheet can be tricky. If one formula is off, it throws off the entire sheet, because many formulas rely on data calculated by other formulas to automate the calculation process. The formulas below can be altered to match the appropriate column and row numbers for your spreadsheet.
To calculate the regular hours worked in a single day, you will need to first set up columns for time in, time out, and hours worked. In our example, we are going to use columns A through E.
In row one, enter the column titles:
- A1: Day of the Week
- B1: Time In
- C1: Time Out
- D1: Hours Worked
- E1: Total Pay (optional)
In row two, format the cells to calculate the total hours worked each day.
- Select B2 and C2
- Open the Format Cells settings by pressing Ctrl 1
- Select the Number tab
- Select the Category list box
- Select time
- Select 1:30 PM from the Type list
- Click OK
- Right-click D2
- Select Format Cells
- Select the Number Tab
- Select 2 decimal places
- Click OK
- To calculate the total number of hours worked, enter this formula: =SUM(C2-B2)*24
If you also want to calculate the total pay for the day:
- Click H1
- Enter the employee's hourly rate
- Click E2
- Enter the formula =SUM(D2*$H$1)
Repeat the process for the required number of days. Alternatively:
- Select the Fill handle (the plus sign at the bottom right corner of the cell) and drag it down the column to fill the cells you want to use this formula.
- Double-check that the formulas in the cell references in the formula match their respective rows.
If you have used rows one and two for the name of the company (or other pertinent information), your column titles will be further down the sheet, so check to make sure the row numbers accurately reflect the row you want to calculate.
How do you calculate total time in Excel?
To calculate the total time in your Excel timesheet, you will need to continue with the work you began in calculating hours worked previously. If any of the calculations for the daily totals were off, the weekly total will also be off.
Assuming a seven-day workweek, you need to add the totals for each day in Column D. To do this:
- Select D9
- Enter the formula =SUM(D2:D8)
If you want to calculate the total pay earned:
- Select E9
- Enter the formula =SUM(E2:E8)
If you are calculating the hours worked in a month, change the cell numbers to accurately reflect the totals you wish to add.
How do you calculate overtime hours in Excel?
To calculate overtime hours in your timesheet in Excel, you will need to have an overtime column in your Excel timesheet. To format an Excel timesheet that allows for overtime calculation, input the following titles into the respective columns:
- A1: Day of the Week
- B1: Time In
- C1: Time Out
- D1: Hours Worked
- E1: Regular Time
- F1: Overtime
- G1: Rate (optional)
- H1: Total Pay (optional)
In row two, format the cells to calculate the total hours worked each day as outlined previously. Additionally:
- Click E2
- Assuming the regular time for each employee is eight hours, enter the formula =MIN(8,D4)
- If the regular time for the employee is something other than eight hours, change the hours worked to reflect the correct number for that employee
To calculate the overtime hours:
- Click F2
- Enter the formula =D2-E2
If you also want to calculate the total pay for the day:
- Enter the hourly rate for the employee in G2
- Click H2
- Enter the formula =(E2*G2)+(F2*G2*1.5)
You will again need to repeat the process for the desired number of days. Or:
- Click the Fill handle and drag it down the column to fill the cells you want to use this formula.
- Double-check that the formulas in the cell references in the formula match their respective rows.
If you used rows one and two for company or employee information, your column titles will be on different rows. Be sure to check the cell column and row accurately reflect the row you want to use in your calculation.
Now that you have set up and checked that all of the formulas are functioning correctly, you need to protect those formulas from accidentally being changed. It is relatively easy to input new formulas into an Excel timesheet and to edit them once they are there. This is great for business owners who want to create their own time-tracking system to increase efficiency and save money. However, it also means that it is easy to accidentally delete or change the formula in a way that causes significant calculation errors.
The risk of this occurring increases exponentially with each additional person who has access to the timesheet. All it takes is one backspace in the wrong place and the complex calculations can be undone. If the error isn't noticed quickly, it can lead to costly errors for the employee, employer, or both.
To protect the formulas in your Excel timesheet, you need to lock the formulas. To do that:
- Press the Control key and then the A key to select all cells in the worksheet
- Press the Control key and then the 1 key to open Format Cells
- Select the Protection tab
- Uncheck the locked box
- Click OK
- Select all cells in the worksheet
- Click on the Find & Select option in the toolbar
- Select Go To Special
- Select Formulas
- Click OK
- Press the Control key and then the 1 key to open Format Cells
- Select the Protection tab
- Check the locked box
- Click OK
- Click the Review tab on the top menu bar
- Select Protect Sheet
- Select the Protect worksheet and contents of locked cells
- If you want a password to override this, add it in the password box
- Click OK
Doing this will minimize the risk of a user changing the formulas while still allowing them to add and edit the necessary data for the Excel timesheet to work as intended.
What is the best source of timesheet templates online?
Even if you know exactly how to formulate the perfect Excel timesheet, it doesn't mean you want to spend the time to do it yourself. Fortunately, there are several great Excel timesheet templates available online. Finding the right timesheet template begins with identifying the type of Excel timesheet you need. The three most popular formats include weekly, biweekly and monthly Excel timesheets.
There are several options available for free weekly Excel timesheet templates. Many of the Excel timesheet templates listed below can be printed and filled out by hand by employees or supervisors, or they can be filled out within Excel to access the automated calculations.
Free weekly timesheet templates
Tracking time weekly is ideal for those employees who are paid weekly or whose overtime is calculated daily. Check out these resources:
- Microsoft: This weekly timesheet allows you to track regular time with lunch breaks, sick leave, vacation hours and total time worked. It also calculates the total pay based on regular and overtime worked for the week.
- Vertex 42: This weekly Excel timesheet template allows you to calculate overtime based on daily or weekly hours worked, it has a section to note break time, and it has additional support for California overtime.
Free biweekly timesheet templates
Biweekly Excel timesheets are great if your company is on a biweekly payroll schedule and you want all of the information for each employee easily available in a single sheet. Here are two options for templates you can use:
- Microsoft: This biweekly Excel timesheet works similarly to the weekly option from Microsoft but allows you to view and track two weeks at a time.
- Vertex 42: These biweekly timesheets allow you to record clock-in and out times, and calculate total time worked. The first option allows you to record one break per day, and the second option has space for two breaks, which is ideal for longer shifts.
Free monthly timesheet templates
If your company pays employees monthly, or you have numerous projects assigned to individual employees, and you want to keep track of how many hours are being spent each month by project, a monthly Excel timesheet may be more appropriate. Consider these options:
- Microsoft: In addition to providing a monthly timesheet, this free template also allows you to track weekly, monthly, or quarterly with totals automatically calculated.
- Vertex 42: This monthly Excel timesheet template allows one employee to record their time worked every day for a month while also coding time to specific projects. This is a great option to determine the actual cost of individual projects.
There are thousands of free timesheet templates available online that have varying degrees of usability. It's important to use a reputable source and to check the functionality of the timesheet once you download it to ensure all the formulas work as intended before having employees use it.
If, after seeing what the process of creating and using an Excel timesheet entails, you think it is too difficult or cumbersome, there are options. Some of the best time-tracking software available makes it easy to get started in minutes instead of hours and runs smoothly with little ongoing maintenance or effort on your part.