OK, the holidays are over so I'm back. Sorry for the delay.
First, about the explanation. This really has more to do with algebra than with Excel, so watch closely:
You need the sum of two parts: a) regular hours (ie up to 40) times the rate
r, plus b) overtime hours (any hours over 40) times (
r times 1.5). Algebraically that might look like this:
Code:
t := total hours worked
h := t up to 40
o := overtime hours
r := regular hourly rate
MIN := the lesser of two values
MAX := the greater of two values
---
h = MIN(t,40) ;that is, t or 40, whichever is less
o = MAX(40-t,0) ;if t<40 then just use 0
pay = hr + 1.5or
pay = r(h + 1.5o) ;simplifying
But watch this:
Code:
h + o = t
h + o + o/2 = t + o/2
h + o(1+1/2) = t + o/2
h + 1.5o = t + o/2
So I can substitute
t +
o/2 back into the formula:
Code:
pay = r(h + 1.5o)
h + 1.5o = t + o/2
pay = r(t + o/2)
In Excel, that looks like this:
Code:
D3 := regular hourly rate
D5 := total hours worked
o = MAX(D5-40,0)
---
=D3*(D5+MAX(D5-40,0)/2)
...which is the formula I gave you.
Now let's take a look at the formula you used:
Code:
=IF(D5>=80,(D5-80)*D3*1.5+D3*80,D5*D3)
Yeah, that should work, although you could make it "D5>80" rather than "D5>=80" in the IF clause, if you want. It
looks more complex, but it really isn't; it just uses IF instead of straight calculation, and it accomplishes the same result.
About using 80 hours: If overtime is calculated weekly (that is, based on whether you worked more than 40 hours in a week rather than more than 80 hours in two weeks), then you
cannot accurately calculate overtime based on the two-week total. You may be paid bi-weekly, but your formula has to know the weekly figure in order to figure OT correctly. There's no way around that.
About "opening the workbook correctly": You know, until you pointed it out I'd never noticed the down arrow to the right of the formula bar. I didn't know it existed. I see now what it does, but I don't know what it has to do with opening the workbook. Maybe post that question in the forum separately, so someone else will notice it and answer.