payroll calculation
 

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)

payroll calculation
 

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)

"Bernie" wrote:

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)

payroll calculation
 

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)

--
Biff
Microsoft Excel MVP


"Bernie" wrote in message
...
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)

payroll calculation
 

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.

--
Regards,

OssieMac


"Bernie" wrote:

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)

payroll calculation
 

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


"Bernie" wrote in message
...
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)