payroll calculation

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)
 
A

akphidelt

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)
 
T

T. Valko

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)
 
O

OssieMac

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.
 
B

Bill Kuunders

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
 

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

Similar Threads

PAYROLL CALCULATION 4
Sumif for payroll 2
Payroll Calculation - Help Needed 3
Excel Timesheet Error 2
TOUGH FORMULA QUESTION - PLEASE HELP! 3
If Formula 2
Overtime 4
Ignore text in timecard calculation 1

Top