Summary

To calculate work hours, taking into account break time that needs to be subtracted, you can use a formula based on the MOD function. MOD is used to handle start and end times that cross midnight. In the example shown, the formula in F6 is:

=MOD(C6-B6,1)-MOD(E6-D6,1)

Generic formula

=MOD(workend-workstart,1)-MOD(breakstart-breakend,1)

Explanation 

At the core, this formula subtracts start time from end time to get duration in hours. This is done to calculate both work time and break time.

MOD(C6-B6,1) // get work time
MOD(E6-D6,1) // get break time

Next, break time is subtracted from work time to get "net" work hours.

This formula uses the MOD function to handle times that cross a day boundary (midnight). By using MOD with a divisor of 1, positive results are unchanged, but negative results (which occur when the start time is greater than the end time) are "flipped" to get a correct duration.

For more details, see: How to calculate number of hours between two times

Formatting time durations

In cases where the calculated time exceeds 24 hours, you may want to use a custom format like [h]:mm. The square bracket syntax [h] tells Excel to display hour durations greater than 24 hours. If you don't use the brackets, Excel will simply "roll over" when the duration hits 24 hours (like a clock).

Alternative timesheet layout

The screenshot below shows an alternative format to capture time worked. Instead of logging work and break time separately, this version captures two separate in/out times for a single shift.

Alternative timesheet layout

For this layout, the formula used in F5 is:

=MOD(C5-B5,1)+MOD(E5-D5,1)

Instead of subtracting break time from work time, we add together the two work times.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.