How to Track Employee Hours in Excel

It is easy to calculate the number of hours an employee has worked by subtracting his start time from his end time. But if the shift spans from noon to midnight or more, then simple subtraction may not provide you with accurate results. To make your job easier, you can create an excel sheet that can track your employee working hours correctly. Here are the steps that you should follow.

1. Select cell A1 and enter Time In.
2. In B1, enter Time Out.
3. In C1, enter the Number of Hours Worked.
4. Open the Format Cells dialog box by pressing Ctrl 1. Select both A2 and B2.
5. A dropdown list will appear. From the Category List box, select Time. Enter the shift starting time here. For example, 2:30 PM. Enter this value in the Type list and select OK.
6. Now right click on C2 and go to Format Cells. Again, check the Category List box and select Time. Enter 14:30 in the Type List and click OK.
7. Now, it’s time to enter the ultimate hour-tracking formula. Go to cell C2 and type this formula: =IF(B2<A2,B2+1,B2)-A2

If you enter 10:00 PM as the shift starting time and 6:00 AM as the shift end time, Excel will show 8 hours worked.

Time tracking templates

Apart from using a formula to create your own tracking sheet, you can also use templates that can work in similar ways. Here are some of the templates you should use:

1. Weekly timesheet

This template is essential if you want to calculate your employees’ worked, amount of wages they should get according to the hours worked, and paid time-off from duty. You can create separate files for every employee to make a clutter-free record.

2. Bi-weekly timesheet

You should create a bi-weekly timesheet if you pay your employees once in two weeks. Like the weekly timesheet, this template also allows you to compute the number of hours your employees worked, their total wages for 14 days, and the total amount of paid time-off.

3. Monthly timesheet

This is the most common template used by many companies. From rate per hour to the number of hours worked, this template lets you calculate everything. The formulae activate automatically whenever you put the time-in and time-out for every employee. You also need to enter the rate per hour. This helps to calculate the total earnings of the employee in a month. You can also get a weekly breakdown of every employee in this template.

4. Project timesheet

Project timesheet is effective if you work with multiple clients and need to track the number of hours you worked on different projects. It is helpful for companies where managers need to track billable hours to prepare invoices correctly.

You can customize any row or columns after downloading these templates. Just make sure you don’t change the formulae of any cell. Apart from that, you can add details like employee number, employee location, job code, or any other information that provides precise details of an employee’s work.