Example and explanation
Figure 1.BasicExcel timesheet formula with breaks
This formula is used to calculate the work hour while taking into consideration the break time. The break time is subtracted to get the normal working hours, and this is done using a formula that operates with a MOD function. The MOD function can deal with a start and end hours that cross noon or midnight. In the cell J10 in the screenshot above the formula is
How the formula works
The formula operates by deducting the start hours from the end hours. After which the duration of the break hours is deducted from the work hours. This gives us the network hours.
MOD(G10-F10,1) // work time
MOD(I10-H10,1) // break time
The MOD function is used with 1 as the divisor. This leaves the network hours returned as positive numbers unchanged while the hour returned as negative are flipped.