Basic timesheet formula with breaks

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar