If you get paid per hour excluding lunch breaks then it can be tedious to calculate manually, especially when the lunch breaks vary in terms of total time. In this tutorial, I will guide you on how to calculate hours worked minus Lunch in Excel.
Using the Sum Function
You can use the sum function to calculate the number of hours worked minus Lunch in Excel. Here are the steps.
1. First type the following formula
=SUM((D2-B2)+(H2-F2))*24
2. Press enter
As you can see from the image above the the hours worked on Monday are 6 full hours
3. Drag down the fill handle to automatically calculate hours worked minus lunch for the remaining days.
Finally, use the sum Function to calculate the number of hours worked for that whole week without a lunch break.
From the above image, a total of 39.17 hours were worked in that week
Tip: To get the hours in decimal the cell holding hours worked should be formatted as numbers.
Download the practice worksheet
Frequently Asked Questions
1. How do I calculate hours worked including lunch break in Excel?
To calculate hours worked including lunch breaks subtract the reporting time from the work End time. For example, if the end time is 16:00 and the reporting time is 9:00 am use the formula. 16:00-9:00 = 7 hrs.
2. What is the formula for calculating hours worked in Excel?
The formula to calculate hours worked in Excel is =Time End – Time Start.
3. How do I subtract hours from a time in Excel?
The best way to subtract hours from time is by using the 24-hour method. First, convert the time into 24 hours then subtract the hours using the minus sign in Excel. Alternatively, you can format the cell containing the time as time and then just use the subtraction sign to subtract hours from time.
4. How do you subtract time?
To subtract time in Excel you need to make sure the cells are formatted as as time. First, select the cells containing the time then right-click and select format cells. Now click on the number tab and select time. You can choose the time format that you wish from the list. Press okay to apply changes.
Use the minus sign to subtract time. For Example =B-A where B is the cell containing old time while A is the cell containing new time.
5. How do I convert start time and end time to hours in Excel?
To convert time into hours in Excel you just need to multiply it by 24 which is the number of hours in a day. Also, make sure that the cell hosting the results is formatted as a number. For example, 1:30 is equal to 1.50 hours.