Job tracking formula

[adammon]

I am trying to capture the working time between a 'Start time' and 'Registering time.' By working time I mean that I want to exclude any breaks, as well as hours not worked if it carried over several days. Our employees typical work day is from 8am to 5pm with a hour long lunch break from 12pm-1pm.

In the example below, rows 2 and 3 go through the employees lunch hour (12pm-1pm) so I would like to subtract that from the duration to get the actual working time.
Row 4 doesn't carry through the lunch hour so the formula would have to stay consistent if it doesn't go through non-working time hours.
Going down to row 6, the employees job carried over to the next day so I would want to subtract the hours from 5pm-8am.

Is there a formula that can work for all occasions so I don't have to make one for each scenario?

Thanks in advance for the help!

 

[jim may]

Does this work for you???? Just Copy D2 down....


Excel 2010

	A	B	C	D
1	Start Time	Registering Time	Duration	Minus Breaks
2	04/07/16 10:53:59 AM	04/07/16 1:17:52 PM	2:23:53	1:23:53
3	04/07/16 10:48:19 AM	04/07/16 1:21:13 PM	2:32:54	1:32:54
4	04/07/16 1:15:48 PM	04/07/16 1:24:40 PM	0:08:52	0:08:52

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C2 =B2-A2 D2 =IF(AND(A2-INT(A2)<TIMEVALUE("12:00:00"),B2-INT(B2)>TIMEVALUE("01:00:00")),C2-TIMEVALUE("01:00:00"),C2) C3 =B3-A3 D3 =IF(AND(A3-INT(A3)<TIMEVALUE("12:00:00"),B3-INT(B3)>TIMEVALUE("01:00:00")),C3-TIMEVALUE("01:00:00"),C3) C4 =B4-A4 D4 =IF(AND(A4-INT(A4)<TIMEVALUE("12:00:00"),B4-INT(B4)>TIMEVALUE("01:00:00")),C4-TIMEVALUE("01:00:00"),C4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>