**General formula**

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

## 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

`=MOD(G10-F10,1)-MOD(I10-H10,1)`

**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.

## Leave a Comment