Using a Lookup Table to Calculate Overtime Payments

Hi all,

I hope someone can solve this problem for me as I've been trying without success for some time!

I want to have a spreadsheet that calculates overtime pay. I have different overtime rates depending on what time it is. If you take a look at the attachment then I hope its becomes clearer as to what I'm looking for. Basically, I want it to do the following:-

"As cell A4 = "Fred" then (17:23hrs to 20:00hrs = 02:37 hours x the overtime rate in cell D9) + (20:01hrs to 22:23hrs = 02:23 hours x the overtime rate in cell E9)"


Any assistance would be greatly appreciated