Calculate the Total Time Worked Minus Lunch Breaks in Excel

Add to Favorites

Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks.

This is actually quite simple once you know it, but too many people overcomplicate this.

Here is our sample worksheet:

(Dont forget to download the accompanying worksheet for this tutorial so you can follow along.)

Formula to Calculate Time Worked Minus the Lunch Break

There are two examples in the worksheet, one where you simply record the amount of time the worker was on break or at lunch, and one where the actual start and finish time of the break or lunch is recorded.  Getting the total time worked for both is fairly straight-forward.

For the first example, we simply subtract the break time from the other time:

Make sure to start right-to-left when you do this.  You want the time from which you subtract the other times to be the latest time in the day.  Since these examples are in military time (24 hour time format) it is quite easy to see how this works, starting with cell C2 in the formula, which is the largest number here.

Now, lets go to the second example:

This time, there is a bit more work involved but it is all simple math.

We are performing two calculations, one to figure out the total time the person was at work (D5-A5) and one to calculate how long the break was (C5-B5) and then we subtract the lunch break from the time at work (the larger time figure) to get the result.

Parentheses are very important here and they must surround the separate calculations or you will not get the correct result.

As you can see, it is fairly simple to get the total time worked when accounting for breaks and lunch time.  Just make sure that the time/number from which you subtract the other time/numbers is the latest time/largest number or it wont work.

Formatting for Time - VERY IMPORTANT

We need to make sure that all of our cells are formatted correctly!!!  This simple thing is the cause of MANY issues when working with time in Excel.

All of our cells have the custom time format of h:mm

When you go to enter a time into a cell, Excel will automatically figure out what time format you should have, so just make sure that it got it correct.

Right-click the cells and then select Format Cells to select the correct number format.  You will see the window below, and you need to go to the Number tab:

The h:mm format selected above is what you should choose if you are entering a simple time.

Make sure that Excel did not throw a date in one of your cells by default though:

Look at my Lunch (time) cell above and how it reads 0:00 and now look to the formula bar where it has a date in front of the time (1/30/1900)!  If you didnt select the cell and look to the formula bar you would not know that there was a hidden date in there, and this breaks our time calculations; as you can see, the Time cell in E2 now reads ######## instead of 7:15 or 7:45.

This kind of error happens when you enter the time in a format that Excel does not correctly read.  In this case, I input 30 instead of 0:30 and that is what caused the error.  To fix the problem just delete the cell contents and change the format of the cell to the correct format, as mentioned above, and re-input the time - you may not have to delete the cell contents to do this but it makes it less confusing when working with smaller data sets.

Quick Tips Going Forward

If your time will exceed 24 hours, make sure to use the time format [h]:mm:ss in the cell that will display the total hours/time worked.

If your time will exceed 24 hours make sure to include dates, using a date-time format, with the start and finish times to make performing the calculations easier.

 


Downloadable Files: Excel File

Similar Content on TeachExcel
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
Macro: This UDF (user defined function) calculates the Future Value of Compound Interest in E...
Get the Number of Workdays Between Two Dates in Excel
Tutorial: How to calculate the total number of working days between two dates in Excel.  This allows...
Insert and Manage Page Breaks in Excel
Tutorial: How to insert, remove, and manage page breaks in Excel.  This can be rather annoying and ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Tutorial: Ill show you how to calculate the difference between two times in Excel when that differe...
SUBTOTAL Function - Work on Filtered Data in Excel
Tutorial: Perform basic functions on a filtered dataset in excel, including SUM, AVERAGE, COUNT, COU...
Return the ISO Week Number from a Date in Excel - UDF
Macro: Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defin...