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.

 

[steve the fish]

Hi. This is in one cell??

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

 

[xblft1]

No, apologies if confusing.
My table has a column for each day of the month and a line for each employee. I can make a mock table if need be.

 

[xblft1]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]8:32:08[/TD]
[TD]8:35:13[/TD]
[TD]7:53:00[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]8:11:14[/TD]
[TD]8:22:17[/TD]
[TD]8:01:13[/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is what my data looks like.
I am trying to avoid the need to create a separate table that calculates overtime for each particular day.
As mentioned before, the formula should compare the daily registered hours to 8:00:00 and sum up the non-negative differences.

Many thanks again for looking.

 

[steve the fish]

Ok maybe one of these:

=SUMPRODUCT(--(B2:AF2>TIME(8,0,0)),(B2:AF2-TIME(8,0,0)))
=SUMIFS(B2:AF2,B2:AF2,">"&TIME(8,0,0))-(TIME(8,0,0)*COUNTIFS(B2:AF2,">"&TIME(8,0,0)))

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	8:00:00	Day 1	Day 2	Day 3
2	Employee 1	8:32:08	8:35:13	7:53:00		01:07:21
3	Employee 2	8:11:14	8:22:17	8:01:13		00:34:44
4
5	or
6	8	Day 1	Day 2	Day 3
7	Employee 1	8:32:08	8:35:13	7:53:00		1.12
8	Employee 2	8:11:14	8:22:17	8:01:13		0.58
9
6a

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(B2:E2>$A$1),(B2:E2-$A$1))
F7	=SUMPRODUCT(--(B7:E7*24>$A$6),(B7:E7*24-$A$6))

 

[xblft1]

Thank you both for the swift responses.
How would those look for calculating time loss?
Here I am looking only to count the days where they worked below 8hrs. I am trying to adapt the formulas but I seem to be missing something.

 

[xblft1]

Forgot to mention - I have days where they worked 0 hours in the list. My trouble is preventing the formula from returning -8 hours for those (when calculating time loss).

many thanks again.

 

[xblft1]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]8:32:08[/TD]
[TD]8:35:13[/TD]
[TD]7:53:00[/TD]
[TD]0:00:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]8:11:14[/TD]
[TD]8:22:17[/TD]
[TD]8:01:13[/TD]
[TD]0:00:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]7:32:22[/TD]
[TD]0:00:00[/TD]
[TD]8:08:11[/TD]
[TD]8:11:22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

A sample table to show my issue when trying to calculate time loss.
Employee 1 has 0:07:00 time loss (7 min on day 3, none on Day 1&2, didn't work on Day 4)
Employee 2 should return 0:00:00
Employee 3 should return 0:27:44

Not sure how to exclude the days with zero worked hours from the results.

Thank you for looking.

 

[Dave Patton]

A few examples would be useful.

Excel 2010
	A	B	C	D	E	F
10	Employee 1	8:32:08	8:35:13	4:53:00		03:07:00
11	Employee 2	8:11:14	4:22:17	8:01:13		03:37:43
12
13	or
14	8	Day 1	Day 2	Day 3
15	Employee 1	8:32:08	8:35:13	4:53:00		3.12
16	Employee 2	8:11:14	4:22:17	8:01:13		3.63
17
6a

Cell Formulas
Range	Formula
F10	=SUMPRODUCT(--(B10:E10<$A$1),--(B10:E10>0),($A$1-B10:E10))
F15	=SUMPRODUCT(--(B15:E15*24<$A$14),--(B15:E15>0),($A$14-B15:E15*24))