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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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.

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.

Thanks.

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!

• You’re most welcome ðŸ™‚ I am glad to know that our tutorials add some value to your life.

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.

Advanced Excel Exercises with Solutions PDF