Excel: How to Calculate Hours Worked Minus Lunch


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:

Excel calculate hours worked minus lunch

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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *