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: 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))*24This 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.
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(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
- MOD(0.375,1) → returns a remainder after dividing 375 by 1.
- 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(0.04166667,1) → returns a remainder after dividing 04166667 by 1.
- MOD(F4-C4,1)-MOD(E4-D4,1) → becomes
- 375-0.04166667
- Output → 8.00 AM
- 375-0.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((F4-INT(F4))*60,"0")& " Mins."Formula Breakdown
- INT(F4) → becomes
- INT(8) → 8
- INT(F4)&” Hrs.” → becomes
- 8&” Hrs.”
- Output → ” 8 Hrs.”
- 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.
- ” 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:
=(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.
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 | Date-Time 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 =(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 🙂
=((E3-C3)-D3)*24
Excellent work.
I have been your online student and very appreciative of your regular, new articles.
Thanks very much.