Need a formula to help job cost Please help!

B

Bianca

I have been trying for the past 2 weeks to develop a formula that will
calculate overtime for workers. I have got as far as this formula
={IF(D30<40,0,SUM(IF(D23:D29-8>0,D23:D29-8),0))}
But the problem with this formula is that it only calculated overtime when a
worker works more than 8 hours in one day. So for example in the case that
the employee works
monday=10 tuesday=8 wednesday=8 thursday=10 friday=8 saturday=8 sunday=8

then on this formula would only calculate 4 hours of overtime because monday
and tuesday they worked 2 extra hours. but the formula doesn't take into
account that 440 hours into the day friday this employee's 5th hour and so on
were overtime because he exceeded 40 hours.
So what I need is a formula that WHEN and IF an employee exceeds 40 hours
then sum all hours over 8 a day. All I need is regular hours and total hours.
Thanks so much
 
D

davesexcel

this could work
=IF(D23>8,D23-8)+IF(D24>8,D24-8)+IF(D25>8,D26-8)+IF(D26>8,D26-8)+IF(D27>8,D27-8)+IF(D30>40,D28)+IF(D30>40,D29)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top