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.
Note that if column F displays times instead of numerical values, you may need to highlight the values in column F and then click the Number Format dropdown arrow from the Home tab and choose General:
This will convert the time values to a numerical format.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Find First Business Day of Month in Excel
How to Add Working Days to a Date in Excel
How to Add Months to Date in Excel
