4 Examples of Timesheet Formula with Lunch Break in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. In office time tracking worksheets, it is quite common to count total work hours. During a day shift office work, lunch break is excluded from work hours. Many offices have varieties of policies regarding this exclusion. This article will discuss four examples of the commonly used timesheet formula with a lunch break in Excel. It applies to multiple cells and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.

Contents hide

📁 Download Excel File

Learn to Create Timesheet Formula with Lunch Break in Excel with These 4 Examples

1. Creating Timesheet Formula Excluding Fixed Lunch Break

2. Establishing Timesheet Formula for Inequal Lunch Break

3. Implementing Timesheet Formula by Shifting to New Worksheet

4. Utilizing SUM Function

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the Excel file below.

Timesheet Formula with Lunch Break.xlsx

Learn to Create Timesheet Formula with Lunch Break in Excel with These 4 Examples

The article is going to introduce four examples to apply the timesheet formula in Excel. The following discussion will cover examples of Fixed lunch break duration, Inequal lunch breaks, New worksheets, and finally, the use of the SUM function. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

Example

1. Creating Timesheet Formula Excluding Fixed Lunch Break

During office hours, most of the offices have a fixed amount of break time to allocate to the employees for lunch. If you fix 0.50,0.75 or 1 hour as a lunch break, you can exclude the time from the working hour very easily. Following is the first example of a timesheet formula with a fixed lunch break in Excel.

⬇️⬇️ STEPS ⬇️⬇️

First, select cell F6.
Second, enter the following formula here. It will subtract entry time from exit time and finally exclude the one hour for lunch.

=(E6-C6)-D6

Third, hit the Enter key.

Fourth, select cell G6.
Fifth, enter the following formula here. It will convert the “Total Time” to the Hour format.

=F6*24

Sixth hit the Enter key.

Seventh, select both F6 and G6 cells, and using the Fill Handle icon, copy the formulas down their cells till all employees are.
Eighth, you will find working time in HH:MM:SS format and Hour format.

approach

2. Establishing Timesheet Formula for Inequal Lunch Break

There are cases where lunch breaks are flexible and calculated as spent by employees. It needs a modified formula to exclude the specific time span for a lunch break. Following is the next example of a timesheet formula with inequal lunch break durations in Excel.

⬇️⬇️ STEPS ⬇️⬇️

Initially, select cell G6.
Next, enter the following formula here. It will subtract entry time from exit time and finally exclude the spent lunch hour.

=(F6-C6)-(E6-D6)

Then, hit the Enter key.

Now, select cell H6.
Here, enter the following formula here. It will convert the “Total Time” to the Hour format.

=G6*24

After that, hit the Enter key.

Next, select both G6 and H6 cells, and using the Fill Handle icon, copy the formulas down their cells till all employees are.
Finally, you will find working time in HH:MM:SS format and Hour format.

📕 Read More: 7 Examples to Calculate Duration of Time in Excel

Example

3. Implementing Timesheet Formula by Shifting to New Worksheet

Lunch breaks and entry and exit times are usually recorded in official practice. Getting the working hour amidst, might make the worksheet bulky and tough to use for more uses. In that case, you can get the work hours after exclusions in a new worksheet. Following, you will get an example that has time records at “Lunch Break” and work hours at “Work Hour Sheet”.

⬇️⬇️ STEPS ⬇️⬇️

In the beginning, select cell C6 from the “Work Hour Sheet”.
Now, to write a formula, type “=(“ on the cell.
Next, click on the “Lunch Break” sheet.

You can manually click on the cells according to the required formula. You can also copy the following formula if you can modify it as your dataset.

=(‘Lunch Break’!F6-‘Lunch Break’!C6)-(‘Lunch Break’!E6-‘Lunch Break’!D6)

Then, hit the Enter key.
Eventually, you will get the total time in the “Work Hour Sheet”.

Now, select cell D6.
Here, enter the following formula here. It will convert the “Total Time” to the Hour format.

=C6*24

After that, hit the Enter key.

Here, select both C6 and D6 cells, and using the Fill Handle icon, copy the formulas down their cells till all employees are.
Therefore, you will find working time in HH:MM format and Hour format.

Example

4. Utilizing SUM Function

This method can help you not only with a lunch break but also with other break time exclusions. The SUM function makes it easier to add all the worked hours and get a final result. This method is formatted in a 24-Hour format for users. Following is the final example of a timesheet formula with multiple breaks along with lunch break in Excel.

⬇️⬇️ STEPS ⬇️⬇️

To begin with, select cell I7.
Next, enter the following formula here. It will subtract entry time from exit time and finally exclude the spent lunch hour.

=SUM((D7-C7),(F7-E7),(H7-G7))

Then, hit the Enter key.

Now, select cell J7.
Here, enter the following formula here. It will convert the “Total Time” to the Hour format.

=I7*24

After that, hit the Enter key.

Next, select both I7 and J7 cells, and using the Fill Handle icon, copy the formulas down their cells till all employees are.
Finally, you will find working time in HH:MM:SS format and Hour format.

📄 Important Notes

🖊️ All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.

🖊️ In the case of the fourth example, you can use the same time for other break times start and end, if there were no breaks taken.

📝 Takeaways from This Article

The article lets the readers understand the variety of options available to generate a list based on criteria in Excel.

📌 Primarily, you can determine work hours easily by a fixed lunch break duration.

📌 You can also calculate hours in case of unequal lunch break durations. Your formula needs an extension in this case.

📌 If you need to prepare work hours in a separate sheet in 24-Hour format, you can use subtraction through sheet values.

📌 The SUM function is helpful as well. It will help you track additional break times as well.

Conclusion

To use a timesheet format with a lunch break in Excel, four examples are common. You can use it for Fixed lunch break duration, Inequal lunch breaks, and New worksheets. You can add the SUM function to calculate time duration in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.