Table of Contents
You can use the following formula in Excel to calculate the total hours worked by an individual minus time spent at lunch:
=((E2-B2)-(D2-C2))*24
This formula makes the following assumptions:
- Cell B2 contains the start time of the shift.
- Cell C2 contains the lunch start time.
- Cell D2 contains the lunch end time.
- Cell E2 contains the end time of the shift.
The following example shows how to use this formula in practice.
Example: Calculate Hours Worked Minus Lunch in Excel
Suppose we have the following dataset in Excel that shows the times worked by various employees at some company:
Suppose we would like to calculate the total hours worked by each employee minus time spent at lunch.
We can type the following formula into cell F2 to do so:
=((E2-B2)-(D2-C2))*24
We can then click and drag this formula down to each remaining cell in column F:
Column F now shows the total hours worked minus lunch for each employee.
For example:
- Andy worked 8.5 total hours, not including time spent at lunch.
- Bob worked 8.25 total hours, not including time spent at lunch.
- Chad worked 8 total hours, not including time spent at lunch.
And so on.
This will convert the time values to a numerical format.