Microsoft Excel has lots of uses in small and medium enterprises that do not use **Customer Relationship Management (CRM)** software. The attendance report is one of the basic needs of a company. This complex work can be done easily by Excel. In this article, we will discuss the **Excel timesheet** formula with a lunch break. Different **timesheet formulas** can also be set up in Excel according to the companyâ€™s need to perform other tasks.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Examples of Excel Timesheet Formula with Lunch Break**

We can make this timesheet formula with a lunch break with different time formats and apply lunch breaks in multiple ways. These processes are described below.

**1. Excel Timesheet Formula with Fixed Lunch Time**

Lunch break procedures vary from one company to other. In this section, we assume that the lunch break is fixed at 1:00 hour. Now, we will find the timesheet formula for this condition.

We will form the timesheet formula for the below data set.

In the data set, we have the **Entry**, **Exit, **and **Lunch** hours. Here lunch hour is fixed for 1 hour, but the entry and exit vary. So, now we find out the working time.

**Step 1:**

- Go to
**Cell F5.** - Write the below formula-

`=((E5-C5)-D5)*24`

**Step 2:**

- Press
**EnterÂ**button.

**Step 3:**

- Drag the
**Fill Handle**icon towards the last cell.

Finally, we get the work hour after deleting the lunch break from the difference between the entry and exit time. We represent the effective work time in terms of hours. We can represent results in terms of hours and minutes also. See the below process.

**Step 4:**

- We add a column named
**Total Work Time (Hrs. & Mins.)**to present the work time in the specified format.

**Step 5:**

- Select the cell of that column and press
**Ctrl+1**. - We get
**Format CellsÂ**window. - Select
**CustomÂ**category. - Type the format as
**hâ€ť Hrs. and â€śmâ€ť Mins.â€ť** - Then press
**OK**.

**Step 6:**

- Now, write the below formula in
**Cell G5**.

`=(E5-C5)-D5`

**Step 7:**

- Then press
**Enter**.

**Step 8:**

- Now, drag the
**Fill Handle**icon towards the last cell.

Finally, we get the result in terms of the **Hours** and **Minutes** format.

**Read More: Excel Timesheet Formula with Lunch Break and Overtime**

**2. Excel Timesheet with Variable Lunch Break**

In this section, we will consider the case of the lunch break of the specific organizations where the lunch break is not fixed. They may offer a period. And in that period the employees can have their lunch with the required time they want to spend.

We will consider the above data set for this condition. Here, the employees can take lunch breaks whenever they want in a specific period.

**Step 1:**

- Now, go to
**Cell G5**. - Write the below formula. Here, we substruct the lunchtime from the total working time.

`=SUM((F5-C5)-(E5-D5))*24`

**Step 2:**

- Then, press
**Enter**and then drag the**Fill HandleÂ**towards the last cell.

This results in the hours unit. But we want to show the result in terms of the **Hours **and **Minutes **form.

**Step 3:**

- Format the cells as shown before.
- Now, go to
**Cell H5**and write down the below formula.

`=(F5-C5)-(E5-D5)`

**Step 4:**

- Then press
**Enter**.

**Step 5:**

- Drag the
**Fill Handle**icon till the last cell containing data.

Finally, we get the result in terms of the **Hours **and **Minutes** format also.

**Read More:** **Timesheet Formula in Excel (5 Examples)**

**Similar Readings**

**[Fixed!] SUM Not Working with Time Values in Excel (5 Solutions)****How to Create a Monthly Timesheet in Excel (with Easy Steps)****Calculate Hourly Rate in Excel (2 Quick Methods)****How to Subtract and Display Negative Time in Excel (3 Methods)****How to Create an Employee Timesheet in Excel (with Easy Steps)**

**3. Different Time Formats with Excel Timesheet Formula**

We can apply the 12-hours or 24-hour format to establish the timesheet formula. The above examples are presented in 24-hours format. Now, we will show one example with the 12-hour format.

We are considering the below data for this section. All the data are in 12-hours format here.

**Step 1:**

- Go to
**Cell F5**. - Write the below formula here.

`=(SUM((C5-B5)+(E5-D5)))*24`

**Step 2:**

- Now, press
**Enter**button.

**Step 3:**

- Then drag the
**Fill Handle**icon and complete the task.

**Step 4:**

- Now, go to
**Cell G5**to present the result in Hours and Minutes format. - Write down the below formula.

`=(C5-B5)+(E5-D5)`

**Step 5:**

- Now, press
**Enter**and pull the**Fill Handle**to the last cell.

**Read More: How to Calculate Time Difference in Excel (13 Ways)**

**Get Total Work Time of a Week Considering the Lunch Breaks**

Here, we will show the total working time of a week.

**Step 1:**

- We add a row named
**Total**in the data set.

**Step 2:**

- Now write the below formula in
**Cell F11**.

`=SUM(F5:F9)`

**Step 3:**

- Now, press
**Enter**.

**Step 4:**

- Similarly, write the following formula on
**Cell G11**and press**Enter**.

`=SUM(G5:G9)`

But the exact result is not showing. It is the reminder of the time, as the value exceeds 24 hours.

**Step 5:**

- Now, click
**Ctrl+1**and get the**Format CellsÂ**window. - Customize type as
**[h]â€ť Hrs. and â€śmâ€ť Mins.â€ť**

**Step 6:**

- Finally, press
**OK**.

This is the exact result in terms of the **Hours **and **Minutes **form.

**Read More:** **How to Calculate Total Hours Worked in a Week in Excel (Top 5 Methods)**

**Conclusion**

In this article, we showed the timesheet formula with the lunch break in Excel. We also showed some additional features. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

**Related Articles**

**How to Calculate Average Handling Time in Excel (2 Easy Ways)****Add Time in Excel Over 24 Hours (4 ways)****Excel Formula To Calculate Time Worked****How to Add Hours to Time in Excel (8 Quick Ways)****Calculate Average Response Time in Excel (4 Methods)****How to Use Time Format in Excel VBA (Macro, UDF, and UserForm)****Excel Formula for Overtime over 40 Hours [with Free Template]****How to Calculate Elapsed Time in Excel (8 Ways)****Excel Formula to Calculate Hours Worked Minus Lunch**