Quote:
Originally Posted by clo312
Hi I was hoping someone could help me with my formula I have, I am basically tring to make a worksheet that calculates pay based on the inputed number of hours. I can make the formula work for either straight time hours or overtime hours depending on what i do to try to make the formula work the right way. I have rivised and revised this formula to try and solve this and not getting no luck. for example if i work 48 hours that would be 8 hours overtime i can make the formula calculate that right then if i work 32 hours it either dont calculate it right or it dont calculate at all. or with changes I made I can get it to calculate all straight time hours and not the overtime. Any help is appreciated
This is the current Formula
=IF(AND(D5>40,D5<=(D5-40)*D3*1.5+D3*40),D5*D3)
D5=Hours worked
D3=hourly wage
D8=where the function goes
|
So you are saying if they worked more than 40 hours and they work less than 40 hours plus other stuff do something
Couple of problems;
In
=if(AND(d5>40,D5<=(D5-40)*D3*1.5+D3*40),D5*D3)) you haven't terminated the AND properly.
An OR would be better but your logic is a little overcomplicated.
Assuming you rewrote it to say
=IF(OR(D5>40,D5<=(D5-40),(D5-40)*D3*1.5+D3*40),D5*D3) then you'd calculate overtime if they'd worked more than 40 hours but it's overly complicated.
How about the following which calculates overtime over 40 hours else normal pay ? Always keep ifs as simple as possible.
=if(d5>40,(D5-40)*D3*1.5+D3*40),D5*D3)
Optionally this works as well
=d3*40 +(if(d5>40,(d5-40)*1.5*d3,0)