Please help with an "if" question

[DonDonDon]

Hello all

I apologies if this is a simple question regarding formulas. I am new to "numbers" and have never used excel. I need to write a formula that will do this:

John works 9 hours at $15/per hour. If John works more than 5 hrs but less than 8 he takes a half hours break (not paid) if he work more than 8 hours he takes an hour break (not paid for)

I need a cell to calculate his pay at the end of his shift.

I am learning as fast as I can but these "if" formulas are a little tricky for me.

If someone could kindly help me I think I can work out the rest from your example.

Thank you very much

[Ads]

[twerppoet]

It was a bit of a challenge, but fun. Hope I'm not doing someone's homework, because figuring these things out is half the lesson.

Anyway, the illustration is not entirely clear, so here are some things to hold in mind.

The IFERROR is a wrapper in case the basic equation gives back an error. This happens when the Wk Hr cell is empty. I'm not certain but I believe it is the DUR2HOURS function that does this. It is not the usual dived by zero problem, because an entry of zero in the Wk Hr cell clears the error.


You can make the formula simpler by not formatting the Wk Hr column as duration as I did; but that means you have to remember to enter your work time in decimal. 1h 30m = 1.5. If you do use Duration as I did, you have to convert it to a decimal value before you test it or use it to compute the Pay. That makes for a lot of DUR2HOURS functions.

There are probably ways to compare the durations directly, but it looked like they would only add another function without lessening the complication of the final formula.

It requires two IF statements, the second nested in the if-false expression of the first. The first tests for the least case, equal too or less than 5 hr. If it is true you get a simple 15 x hr computation. If false you still have two cases to differentiate, so you need the second if statement. You don't have to test for less than or equal to 5 again, if it were true you'd already computed the answer.

In the second (nested) IF statement you test whether the Wk Hr are less than or equal to 8. If so you subtract the 30 minutes (.5 hours) from the total hours then multiply by 15.

Anything else will obviously be greater than 8 hours, so in the if-false expression of the nested IF statement you subtract 1h from the total and multiply by 15.

I did a casual test to see if the formula worked (which is why I ended up adding the error wrapper), but I did not rigorously test for all possible inputs. For instance, I did not try to see what would happen if I entered a negative number.

Hopefully this plus the illustration should be enough for you to figure out what is going on. Don't miss the manually entered parentheses. They are necessary to get the right results.