Subtracting 8 Hrs to calculate OverTime with no values showing error

[fredieusa]

Hello All,

My again! The noobie..

Cell B2 is not calculating as the values in I8 onwards do not appear good. I7 calculates it well as there are times. How do I modify my formula for the I column to show a zero id there are no times in columns F, G and H ?

Thank you.

 

[bosco_yip]

I7, copied down :

=IF(H7,H7-8/24,0)

Regards
Bosco

 

[fredieusa]

Hi Bosco, Your formula works great BUT only if there is Overtime, ie, total hours worked is over 8. If the guy only works 6 hours, then it does not calculate :-(

 

[fredieusa]

Nope, the below just gives a 0 as a result to all values :-(

OK, through Trial and error i came up with..
=IF(T7>=8,T7-8/24, 0)

 

[vletm]

fredieusa
There are many kind of rules of working hours and ... 'over times hours'.
As in Your sample,
do You mean 'total worked hours' per 'shift'?
I would solve these per day.
... and also I would use 'scheduled times' too
... or why there are those time?
Few 'soft' samples way to calculate ...

 

[fredieusa]

Hi vletm.

for this calculation, anything over 8 hours per shift (regardless of the change in day) is considered over time (OT). If total hours worked is 9 then, OT is 1 hour.

B2 calculates total OT hours for all employees for the week. Column I should show a zero OT if total hours worked (H) is 8 or less, instead it has some negative value.

I would like for the I to show a zero instead IF the OT is zero.

 

[p45cal]

=MAX(0,H7 - 8/24)

 

[fredieusa]

=MAX(0,H7 - 8/24)

Genius!