0

I have simplified this as much as I can:

I would like to keep track of when overtime is being worked and by who.

sample

So in the sample, I have four employees over a period of five days working hours both above and below their contracted daily hours.

In the outlined cells I want to return how much time each employee worked beyond their contracted daily hours and how many hours employees worked beyond their contracted hours on each day. I am not interested in days where less hours were worked.

I hope this makes a bit more sense and many thanks to those who have aided me so far.

4
  • 1
    You could try =IF(statement, true, false)... support.office.com/en-us/article/…
    – Dave
    Jun 24, 2016 at 16:18
  • Thanks, Dave. I'm probably being a bit slow, but I'm not really having any luck with that. Would you be able to provide an example of how I could use the if statement to sum only the hours above the contracted amount?
    – Mike
    Jun 27, 2016 at 10:23
  • Your question is very unclear. You wrote Column C should have a total of... Erm, how does a column have a total? A cell can have a total! Did you mean C4?
    – Dave
    Jun 27, 2016 at 10:40
  • I will attempt to update more coherently this evening, thanks for your patience.
    – Mike
    Jun 27, 2016 at 11:25

1 Answer 1

1

You can achieve it with e.g. SUMPRODUCT:

  • Total overtime by employee:
    =SUMPRODUCT((D2:H2>C2)*(D2:H2-C2))
  • Total overtime by day:
    =SUMPRODUCT((D2:D5>$C$2:$C$5)*(D2:D5-$C$2:$C$5))

The logic is the same for both: first part check which employee / day has overtime, then sum those employees / days.

Alternatively you can use this formula too (this to be entered to D6):
=SUM(IF(D2:D5>$C$2:$C$5,D2:D5-$C$2:$C$5,0))
It is an array formula (need to press CTRL+SHIFT+ENTER after it) and also a bit longer, but probably better shows the logic of the formula.

enter image description here

1
  • 1
    That's great! I'm working with more complex data than this and a few more elements, but couldn't see what I'd done wrong. I simply started again with your formulae and it worked first time; thank you very much!
    – Mike
    Jun 28, 2016 at 12:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .