Timesheet Formula in Excel (5 Examples)

To calculate the time difference (e.g. work hours) between two time periods, we create a timesheet. There are several ways that we can use to keep track of time on a timesheet. Using formulas in Excel timesheets can make it easier and more convenient to apply. In this regard, this article will help you learn the timesheet formula in Excel with 5 relevant examples.

Download the Practice Workbook

You can download the Excel file from the link below and practice along with it.

5 Examples Relevant to Timesheet Formula in Excel

Let’s assume that we have a data table, Employee Work Hour Timesheet. We will use this dataset to demonstrate all the examples regarding the timesheet formula in Excel one by one.

1. Excel Timesheet Formula: Using Simple Subtraction

We have a worksheet of four columns data table. The data table stores the entry and exit time of employees and calculates their total work hours on daily basis. The first column stores employee names, the second column holds entry time, the third column contains exit time, and the last column keeps the work hours count.

Now we can apply the simple arithmetic subtraction formula within this Excel timesheet. To do that, follow the steps below:

❶ First of all type of formula is below within cell E5.

❷ After that press the ENTER button to execute the subtraction formula.

❸ Finally drag the Fill Handle icon to the end of the Work Hours column.

Read more: How to Subtract and Display Negative Time in Excel (3 Methods)

2. Excel Timesheet Formula: Using MOD Function

Instead of using the simple arithmetic subtraction formula, we can use the MOD function to do so. Actually, we will use the subtraction formula inside the MOD function, within its argument list.

The MOD function has two arguments in total. In place of the first argument, we will insert the subtraction formula, and the second argument requires a divider value. Which will be 1 for this instance.

Now follow the steps below to learn the procedure:

❶ Select cell E5 to insert the formula below:

❷ After that, press the ENTER button.

❸ Lastly, drag the Fill Handle icon to the end of the Work Hours column.

That’s all.

Read More: How to Create an Employee Timesheet in Excel (with Easy Steps)

3. Excel Timesheet Formula: Using MOD Function Considering Break

In this second, we will again apply the MOD function into the timesheet Excel formula. But this time we will consider the work break to calculate the Net Work Hours by each of the employees.

To calculate the Net Work Hours, we must subtract the break period from the total office time period. So, we will use two MOD functions in total.

The first MOD function will return the total office work period, whereas the second MOD function will return the total break period. By subtracting these two results, we can easily get the Net Work Hours by each of the employees.

Now follow the steps below to learn it step by step:

❶ Type the timesheet formula below within cell G5.

❷  Now press the ENTER button.

❸ At the end, draw the Fill Handle icon to the end of the Net Work Hours column.

Read More: Excel Timesheet Formula with Lunch Break (3 Examples)

Similar Readings

• Excel Timesheet Formula with Lunch Break and Overtime
• How to Create a Monthly Timesheet in Excel (with Easy Steps)
• Create a Weekly Timesheet in Excel (with Easy Steps)
• How to Track Comp Time in Excel (with Quick Steps)

4. Excel Timesheet Formula: Using Simple Addition Formula

This time we will calculate the total work hours in a different way. We will use the simple arithmetic addition formula to calculate the work hours of each of the employees.

So, we’ve divided the work hour count into two categories. Those are total work hours before the break and again total work hours after the break.

Now we can calculate the total work hours simply by adding work hours before the break to the work hours after the break.

To do that, follow the steps below:

❶ At first type the formula below within cell E5.

❷ Then press the ENTER button.

❸ Finally, pull the Fill Handle icon to the bottom of the Work Hours column.

That’s it.

Read More: How to Calculate Hours Worked Minus Lunch with Excel Formula

5. Excel Timesheet Formula: Using SUM Function

Now instead of using the simple addition formula, we will use the SUM function. Well, here one thing is to mention. That is though both of the formulas serve the same purpose, still, there’s a difference.

When there is only a little cell reference to add, then we can use either the simple addition formula or the formula with the SUM function.

But when you have to sum up a large number of cell references, then there’s no alternative to using the SUM function.

Now, follow the steps below to do that,

❶ Select cell E5 and type the formula below:

❷ Press the ENTER button.

❸ Draw the Fill Handle icon to the end of the Work Hours column.

Related Content: [Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)

Things to Remember

📌 As you are working with the timesheet, always set the cell format to Time.

Conclusion

To sum up, we have discussed 5 relevant examples to demonstrate the usage of the timesheet formula in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.

Related Articles

• How To calculate hours worked & overtime [with template]
• Excel Formula for Overtime over 40 Hours [with Free Template]
• How to Track Time Spent on Projects in Excel (5 Useful Methods)
• Create Real Time Tracker in Excel (with Detailed Steps)
• How to Track Employee Vacation Time in Excel (with Easy Steps)