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
 We’ll work with two time formats: 12hour (AM/PM) and 24hour.
 The lunch start and end times are given.
1.1. Showing 12Hour Format (AM/PM time formatting)
 Enter the following formula in cell G4:
=SUM((D4C4)+(F4E4))*24
This formula can also be written as:
=SUM((F4C4)(E4D4))*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 24Hour Format
In 24hour time format, the 13:30 time formatting is applied without the Working Hours column.
 Enter the following formula in cell G4:
=SUM((D4C4)+(F4E4))*24
 Drag down the fill handle to calculate total working hours.
We will get the total working hours for the working days.
Read More: How to Calculate Hours and Minutes for Payroll Excel
Method 2 – Using MOD Function
 Subtract lunch hours from total hours using the MOD function in cell G4:
=MOD(F4C4,1)MOD(E4D4,1)
Formula Breakdown
 F4C4 → becomes
 750.375 → 0.375
 MOD(F4C4,1) → becomes
 MOD(0.375,1) → returns a remainder after dividing 375 by 1.
 Output → 0.375
 MOD(0.375,1) → returns a remainder after dividing 375 by 1.
 E4D4 → becomes
 583330.5416667 → 0.04166667
 MOD(E4D4,1) → becomes
 MOD(0.04166667,1) → returns a remainder after dividing 04166667 by 1.
 Output → 0.04166667
 MOD(0.04166667,1) → returns a remainder after dividing 04166667 by 1.
 MOD(F4C4,1)MOD(E4D4,1) → becomes
 3750.04166667
 Output → 8.00 AM
 3750.04166667
 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:
Read More: How to Calculate On Time Delivery Performance in Excel
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((F4INT(F4))*60,"0")& " Mins."
Formula Breakdown
 INT(F4) → becomes
 INT(8) → 8
 INT(F4)&” Hrs.” → becomes
 8&” Hrs.”
 Output → ” 8 Hrs.”
 8&” Hrs.”
 F4INT(F4) → becomes
 88 → 0
 TEXT((F4INT(F4))*60,”0″) → becomes
 TEXT(0,”0″) → 0
 INT(F4)&” Hrs.”&” “&TEXT((F4INT(F4))*60,”0″)& ” Mins.” → becomes
 ” 8 Hrs.”&” “&0& ” Mins.”
 Output → 8 Hrs. 0 Mins.
 ” 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.
Read More: How to Calculate Production per Hour in Excel
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:
=(E4C4)D4

 (E4C4) calculates the total hours worked.
 D4 subtracts the lunch hour.
 Drag down the fill handle to calculate working hours for other days.
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.
Your Total Time column will display the results in the desired format.
Read More: How to Calculate Total Hours Worked in a Week in Excel
Practice Section
To practice, we have added a practice portion on each sheet on the right side.
Download Workbook
You can download the practice workbook from here:
Related Articles
 How to Calculate Billable Hours in Excel
 Excel Formula for Overtime over 8 Hours
 How to Calculate Hours Worked and Overtime Using Excel Formula
 Excel Formula to Calculate Overtime and Double Time
 Excel Formula To Calculate Time Worked
 Man Hours Calculation in Excel
 How to Create an Injection Molding Cycle Time Calculator in Excel
<< Go Back to Calculate Hours  Calculate Time  DateTime in Excel  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
People normally pay me for this and you are giving it away!
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.
Please, download the working file (it’s free) to see if anything is missing in this article.
Thanks.
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.
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 =(E3C3)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 🙂
=((E3C3)D3)*24
Excellent work.
I have been your online student and very appreciative of your regular, new articles.
Thanks very much.