How to Calculate Hours Worked Minus Lunch with Excel Formula

In this article, we will show you the Excel formula to calculate hours worked minus lunch. Lunchtime can be in hours or it can be expressed as in starting and ending time. Based on different scenarios the formulas will be applied which will give you the number of hours worked excluding lunchtime.


Download Workbook


4 Ways to Calculate Hours Worked Minus Lunch Using Formula in Excel

Here, we have the following dataset containing the records of Start Time, End Time, Lunch Start time, and Lunch End time of some working days. Using this dataset we will calculate the working hours for these days.

excel formula to calculate hours worked minus lunch

For completing this article, we have used the Microsoft Excel 365 version, but you can use any other version at your convenience.


Method-1: Using SUM Function to Calculate Hours Worked Minus Lunch Showing Result in Hour Format in Excel

We will work here in 2 times format. In each case, the lunch starts and lunch ends at the time given. You will get the hours results in fractions like 8.25, 7.75, etc. Don’t get confused if you find these fraction numbers. As we will find the differences at some different times, the fractional result is expected. The 8.25 means 8 hours 15 minutes whereas 7.75 means 7 hours and 45 minutes. In the same way, 8.50 means 8 hours 30 minutes. The formulas for calculating the number of working hours are placed for two different time formats. You will get the same results from both formats. For this method, we will be using the SUM function.


1.1. Showing 12-Hour Format

Here, we will be working with the times in 12 hours time format, which is AM/PM time formatting.

showing 12-hour format to calculate hours worked minus lunch using Excel formula

  • Type the following formula in cell G4.
=SUM((D4-C4)+(F4-E4))*24

Also, this formula can be written as the following.

=SUM((F4-C4)-(E4-D4))*24
  • Drag down the Fill Handle

SUM function

In this way, we will get the total working hours for the working days.


1.2. Showing 24-Hour Format

In 24 hours time format, the 13: 30-time formatting is applied without the Working Hours column.

24 hour format to calculate hours worked minus lunch using Excel formula

  • Enter the following formula in cell G4.
=SUM((D4-C4)+(F4-E4))*24
  • Drag down the Fill Handle

Afterward, we will get the total working hours for the working days.

Read More: How to Calculate Overtime over 40 Hours Using Excel Formula


Method-2: Using MOD Function

In this section, we are going to use the MOD function to calculate the working hours after subtracting the total hours from the lunch hours.

using MOD function to calculate hours worked minus lunch

  • Use the following formula in cell G4.
=MOD(F4-C4,1)-MOD(E4-D4,1)

Formula Breakdown

  • F4-C4 → becomes
    • 75-0.375 → 0.375
  • MOD(F4-C4,1) → becomes
    • MOD(0.375,1) → returns a remainder after dividing 375 by 1.
      • Output → 0.375
  • E4-D4 → becomes
    • 58333-0.5416667 → 0.04166667
  • MOD(E4-D4,1) → becomes
    • MOD(0.04166667,1) → returns a remainder after dividing 04166667 by 1.
      • Output → 0.04166667
  • MOD(F4-C4,1)-MOD(E4-D4,1) → becomes
    • 375-0.04166667
      • Output → 8.00 AM

As a result, the working hours will be displayed in the following time formats which we will change to show the differences.

result

  • Select the range of the working hours and then press CTRL+1.

Afterward, the Format Cells dialog box will appear.

  • Select Time as Category, 13:30 as Type.
  • Press OK.

format cells dialog box to calculate hours worked minus lunch using Excel formula

The final results will be like the following figure.

Read More: How to Subtract Hours from Time in Excel (2 Easy Ways)


Similar Readings


Method-3: Calculate Hours Worked Minus Lunch Showing Result with Hours and Minutes in Excel

To avoid the fraction numbers, you can display your results as Hours and Minutes together. For this, you need an extra formula. Before that, we will use the previous formula in Method-1 to calculate the total working hours. After that, we will do some formatting using the INT, and TEXT functions. The formulas are the same for both 12 hours and 24 hours format styles.

calculate hours worked minus lunch using Excel formula and showing results with hours and minutes

  • Follow Method-1 to fill up the Working Hours column with the formula.

  • In cell G4, apply the following formula for formatting.
=INT(F4)&" Hrs."&" "&TEXT((F4-INT(F4))*60,"0")& " Mins."

Formula Breakdown

  • INT(F4) → becomes
    • INT(8) → 8
  • INT(F4)&” Hrs.” → becomes
    • 8&” Hrs.”
      • Output → ” 8 Hrs.”
  • F4-INT(F4) → becomes
    • 8-8 → 0
  • TEXT((F4-INT(F4))*60,”0″) → becomes
    • TEXT(0,”0″) → 0
  • INT(F4)&” Hrs.”&” “&TEXT((F4-INT(F4))*60,”0″)& ” Mins.” → becomes
    • ” 8 Hrs.”&” “&0& ” Mins.”
      • Output → 8 Hrs. 0 Mins.
  • Drag down the Fill Handle

INT, TEXT FUNCTIONS

Finally, you will have the following result.

Read More: How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)


Method-4: Applying Formula to Calculate Working Hours When Lunch Hour Is Given

Instead of Lunch Starts and Lunch Ends time, only the Lunch Time (In Hours) can be given. In this case, showing the hours of work becomes difficult due to the formatting. To avoid this problem follow the below procedure.

Applying Formula to Calculate Working Hours when Lunch Hour Is Given

  • Enter the following formula in cell F4.
=(E4-C4)-D4
  • Drag down the Fill Handle

As a result, you will see the working hours in the following time formats which we will change to show the differences.

  • Select the range of the working hours and then press CTRL+1.

Afterward, the Format Cells dialog box will appear.

  • Select Custom under the Category section and write down h” Hrs. and “m” Mins.” In the Type
  • Press OK.

format cells dialog box to Calculate Hours Worked Minus Lunch Using Formula in Excel

Afterward, you will have the following results in the Total Time column.

Read More: Excel formula to calculate hours worked & overtime [with template]


Practice Section

For doing practice, we have added a Practice portion on each sheet on the right portion.

practice


Conclusion

In this article, we tried to show the ways for Excel formulas to calculate hours worked minus lunch. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

8 Comments
  1. People normally pay me for this and you are giving it away!

  2. This is a lot of detail, well done.

    Only problem is, you’re not showing the formula for the F column – Excel doesn’t handle sum/subtraction for time correctly on its’ own.

  3. I have taken several of your courses and really appreciate these frequent tips. Excel is so powerful and extensive that I doubt I will ever use it to its potential but I do appreciat these insightful learning aids. Thank you so very much!

  4. Hi there this is the most easy to follow advice out there so thankyou so much, please can I ask when you have a start time, total lunch time and end time and then the total hours and minutes worked such as =(E3-C3)-D3, how to I convert this into a fraction> You do have have this as a end formual above where you have start time, start lunch, end lunch and end time and the end formual is worked out in hours like 6.75hrs etc, but I need it when I only have start and finidhs time and, lunch break total – any advice most welcome 🙂

  5. Excellent work.

    I have been your online student and very appreciative of your regular, new articles.
    Thanks very much.

Leave a reply

ExcelDemy
Logo