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.


📁 Download Excel File

Download the Excel file below.


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.

Creating timesheet formula excluding fixed lunch break in Excel

  • 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.

making timesheet formula excluding fixed lunch break in Excel

📕 Read More: Calculate Hours Between Two Dates and Times in Excel Excluding Weekends


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.

Establishing timesheet formula for inequal lunch break in excel

  • 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.

Timesheet formula generation for inequal lunch break

📕 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)

Implementing timesheet formula by shifting to new worksheet in excel

  • 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.

Using timesheet formula by shifting to new worksheet for getting work hours

  • 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.

📕 Read More: 5 Ways to Calculate Total Hours Worked in a Week in Excel


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.

Utilizing SUM function to get timesheet formula with lunch break

  • 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.

Utilizing SUM function to get add breaks along with lunch break for timesheet formula

📕 Read More: 5 Easy Ways to Fix When SUM Time Is Not Working in Excel


📄 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.


Related Articles

(Visited 47 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo