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.

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

**๐ 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.

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

**๐ 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.

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

**๐ 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

**4 Easy Ways to Calculate Percentage of Time in Excel****How to Calculate Average Response Time in Excel****9 Tricks to Calculate Time Difference Between AM and PM in Excel****4 ways to Create Excel Formula for Overtime Over 8 Hours****2 Ways to Calculate Travel Time Between Two Cities in Excel**