How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods)

Dataset Overview

We will use 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.

Method 1 – Using SUM Function to Calculate Hours Worked Minus Lunch (Hour Format)

We’ll work with two time formats: 12-hour (AM/PM) and 24-hour.
The lunch start and end times are given.

1.1. Showing 12-Hour Format (AM/PM time formatting)

Enter the following formula in cell G4:

=SUM((D4-C4)+(F4-E4))*24

This formula can also be written as:

=SUM((F4-C4)-(E4-D4))*24

Drag down the fill handle to calculate total working hours.

We will get the total working hours for the working days.

1.2. Showing 24-Hour Format

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

Enter the following formula in cell G4:

=SUM((D4-C4)+(F4-E4))*24

Drag down the fill handle to calculate total working hours.

We will get the total working hours for the working days.

Method 2 – Using MOD Function

Subtract lunch hours from total hours using the MOD function 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

Drag down the fill handle to calculate total working hours.

The working hours will be displayed 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.

The Format Cells dialog box will appear.

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

Herewith the final results:

Method 3 – Displaying Hours and Minutes

Calculate the total working hours using Method 1.

In cell G4, enter 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 to apply the formula to other cells.

This will display the result as 8 Hrs. 0 Mins.

Method 4 – Calculating Working Hours When Lunch Hour Is Given

If you only have the lunch time (in hours), follow this method.

In cell F4, enter the formula:

=(E4-C4)-D4

- (E4-C4) calculates the total hours worked.
- -D4 subtracts the lunch hour.

Drag down the fill handle to calculate working hours for other days.

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

To format the results, follow these steps:
- Select the range of working hours.
- Press CTRL+1 to open the Format Cells dialog box.

Under the Category section, choose Custom.
In the Type field, enter h” Hrs.” and “m” Mins.
Press OK.

Afterward, your Total Time column will display the results in the desired format.

Practice Section

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

Download Workbook

You can download the practice workbook from here:

Calculate-Hours-Worked-Minus-Lunch.xlsx

<< Go Back to Calculate Hours | Calculate Time | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

8 Comments

Nollie Jul 30, 2018 at 4:13 PM

People normally pay me for this and you are giving it away!
Jim Nov 7, 2018 at 9:56 PM

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.
- Reply
  Kawser Dec 25, 2018 at 3:40 PM
  
  Please, download the working file (it’s free) to see if anything is missing in this article.
  Thanks.
Bill Feb 13, 2019 at 8:16 PM

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!
- Reply
  Kawser Feb 14, 2019 at 4:26 PM
  
  You’re most welcome 🙂 I am glad to know that our tutorials add some value to your life.
Wayne Williams Jun 26, 2019 at 1:45 PM

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 🙂
- Reply
  Larry A Joyner Dec 1, 2019 at 8:40 PM
  
  =((E3-C3)-D3)*24
Uche Uche Dec 30, 2020 at 6:43 PM

Excellent work.

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