Some Excel help

[Lear_doctor]

Hello all. - Need some additional brain power if someone could oblige I would be grateful

I am working on a spreadsheet that calculates how much to charge when an engineer has to work away from base. The engineers themself's get paid overtime when they work outside the hours of 08:00 to 16:30.

I would like the line on the sheet to look like


Depart time ....... Arrival time ........ Total cost...........

If the engineer departs at say 08:00 and the arrival time was 17:30 I would have 8.5 hours at 'Normal' time and 1 hours at overtime, which is at time and half

So the answer to the sum would be 8 * £60 (say) and 1 * £90 = £570

But how do I write the formula so that it 'knows' that any hours outside the boundries of 08:00 thru 16:30 are to be charged at 1.5 times the normal rate?

Thanks in advance

The Doc

[bnt]

I wouldn't try to do it all in one formula; rather, I'd split the problem in to multiple cells. My way involves using formulas to split off the overtime hours. Rather than try to explain it, I threw an example together, here. (The IF formulas in C2 and D2 are because you don't want negative hours popping up.)

[Lear_doctor]

That's perfect. Thanks for taking the time to help me out, I really appreciate it

regards


The Doc

[bnt]

I should have mentioned that you can hide rows and columns in Excel, so you can spread a calculation out but still make it look neat and tidy. This helps especially when you come back to spreadsheet after a year, and find yourself wondering "what the hell does this formula do?"