Thread: Overtime & Double Overtime - IF function

I'm not sure if I'm reading this right or not, but I tried a SUMIF based on the value of column A looking for OT, DOT and DT.

Try putting...

=SUMIF($A$6:$A$20,"=OT",$I$6:$I$20)

In cell I22

=SUMIF($A$6:$A$20,"=DOT",$I$6:$I$20)

In cell I23

=SUMIF($A$6:$A$20,"=DT",$I$6:$I$20)

...finally, in I24

This returns:

       H           I
21   TOTAL      16.00
22   ST          4.00
23   OT          4.00
24   DT            -

Thanks for the help but it doesn't calculate. Perhaps I did not explain well enough. I want the total hours split (regarless of code) to reflect the following:

Example 1:
19 hours- total
8 hours - regular (time less than or = to 8 hours)
4 hours - overtime (time greater than 8 hours but less than 12 hours)
7 hours -double overtime (time over 12 hours in a 24 hour period)

Example 2:
12 hours- total
8 hours - regular (time less than or = to 8 hours)
4 hours - overtime (time greater than 8 hours but less than 12 hours)
0 hours -double overtime (time over 12 hours in a 24 hour period)


Example 3:
23 hours- total
8 hours - regular (time less than or = to 8 hours)
4 hours - overtime (time greater than 8 hours but less than 12 hours)
11 hours -double overtime (time over 12 hours in a 24 hour period)


Hope this helps

OK, I think I may have it. The Overtime was the tricky one, because if the Time isn't between 8 and 12, you still want to return a number of overtime hours if Time is greater than 12.

For the following I have used D4 as the cell with Time in it, and these 3 formula are in cells D6, D7 and D8:

=IF(D4>=8,8,D4)
=IF(AND(D4>8,D4<=12),D4-8,D4-D6-D8)
=IF(D4>12,D4-12,0)

That works perfect. Thanks so much!