payroll calculation

  • Thread starter Thread starter Bernie
  • Start date Start date
B

Bernie

First let me thank all of you that have helped me get this far.

I am calculating payroll for a 2 week period and once 40 hours per week are
hit the column for regular time stops at 40 and the remainder goes into the
overtime column. I made this work but if week one has less than 40 hours it
will not calculate overtime for week 2 and vise versa.

what is wrong with my formula? =((SUM(E8:K8)-40))+((SUM(L8:R8)-40)
 
Im not sure if I understand correctly but what Im thinking is that you have
to tell the formula to use 0 if the first weeks value is under 40... so it
would look something like this

=IF(SUM(E8:K8)<40,SUM(E8:K8),Sum(E8:K8)-40)+IF(SUM(L8:R8)<40,SUM(L8:R8),Sum(L8:R8)-40)
 
Try it like this to calulate *both weeks* together:

=MAX(SUM(E8:R8)-80,0)

Or, like this to calculate *each week* separately:

=MAX(SUM(E8:K8)-40,0)+MAX(SUM(L8:R8)-40,0)
 
Hi Bernie,

The following should work.

=IF(SUM(E8:K8)-40>=0,SUM(E8:K8)-40,0)+IF(SUM(L8:R8)-40>=0,SUM(L8:R8)-40,0)

If either week calculates to less than 40 then you need to make them zero.
 
A bit longer but it works


=IF((SUM(E8:K8)-40)>0,(SUM(E8:K8)-40),0)+IF((SUM(L8:R8)-40)>0,(SUM(L8:R8)-40),0)

Greetings from New Zealand
 
Back
Top