Calculate the Total Time Worked Minus Lunch Breaks in Excel

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.