Calculate overtime/lost time from a range of cells.

[xblft1]

Hi all,

I have a table of how many hours each employee worked in each day of the month.
I am trying to calculate their overtime and any time loss without having to create new tables.

Sumifs was my 1st thought however it adds cell values where what i want is to add the cell value minus 8:00:00 when this is not negative.

Example of what the data looks like:

Employee 1: 8:32:08, 8:35:13, 7:53:00

The result here should be 1:07:21 (overtime from the 1st two days, no discount for the 3rd day as the loss is to be calculated separately).

My data is a large number of staff for a whole month (and possibly will be a periodic task) so I'm looking for formulas to get those figures for each employee easily.

Any help would be greatly appreciated.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
20	Day 1	Day 2	Day 3	Day 4
21	Employee 1	8:32:08	8:35:13	7:53:00	0:00:00		00:07:00
22	Employee 2	8:11:14	8:22:17	8:01:13	0:00:00		00:00:00
23	Employee 3	7:32:22	0:00:00	8:08:11	8:11:22		00:27:38
24
6a

Cell Formulas
Range	Formula
G21	=SUMPRODUCT(--(B21:E21<$A$1),--(B21:E21>0),($A$1-B21:E21))
G22	=SUMPRODUCT(--(B22:E22<$A$1),--(B22:E22>0),($A$1-B22:E22))
G23	=SUMPRODUCT(--(B23:E23<$A$1),--(B23:E23>0),($A$1-B23:E23))

 

[xblft1]

@Dave Patton thank you so much, you've made my a day a lot better. Hope you'll have a great day yourself.

 

[Dave Patton]

Thanks for the feedback.