Some Excel help
 

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

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

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

regards


The Doc

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?" :8