Excel Timesheet Formula with Lunch Break (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Use Excel Timesheet Formula with Lunch Break: 3 Examples

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

Excel Timesheet Formula with Fixed Lunch Time

Step 2:

  • Press the Enter button.

Step 3:

  • Drag the Fill Handle icon towards the last cell.

Excel Timesheet Formula with Fixed Lunch Time

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. 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 the Format Cells window.
  • Select the Custom category.
  • Type the format as h” Hrs. and “m” Mins.”
  • Then press OK.

Excel Timesheet Formula with Fixed Lunch Time

Step 6:

  • Now, write the below formula in Cell G5.
=(E5-C5)-D5

Excel Timesheet Formula with Fixed Lunch Time

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.


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

Timesheet with Variable Lunch Break in Excel

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)

Timesheet with Variable Lunch Break in Excel

Step 4:

  • Then press Enter.

Step 5:

  • Drag the Fill Handle icon till the last cell containing data.

Timesheet with Variable Lunch Break in Excel

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


3. Different Time Formats with Excel Timesheet Formula

We can apply the 12-hour or 24-hour format to establish the timesheet formula. The above examples are presented in a 24-hour 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-hour format here.

Step 1:

  • Go to Cell F5.
  • Write the below formula:
=(SUM((C5-B5)+(E5-D5)))*24

Different Time Format with Excel Timesheet Formula

Step 2:

  • Now, press the Enter button.

Step 3:

  • Then drag the Fill Handle icon and complete the task.

Different Time Format with Excel Timesheet Formula

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)

Different Time Format with Excel Timesheet Formula

Step 5:

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


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)

Get Total Work Timesheet of a Week

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

Get Total Work Timesheet of a Week

Step 6:

  • Finally, press OK.

Get Total Work Timesheet of a Week

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


Download Practice Workbook

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


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. Keep learning new Excel features and give your suggestions in the comment box.


<< Go Back to Timesheet | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo