Using a Lookup Table to Calculate Overtime Payments

[Alun]

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

[DBY]

Hello Alun
Maybe something like:

Please Login or Register  to view this content.

This gives £61.92 for Fred. Is that correct?

*Have amended formula since first post to account for 'Left Site' times after midnight. Peterrc's alternative solution, however, might be the way to go.

DBY

[peterrc]

Hi Alun,

Please see attached spreadsheet.
Explanation inluded.
Please let me know how you get on.

Regards

peterrc

[Alun]

Many thanks Peterrc and DBY for your suggestions. I'm out of the office for the next couple of days but will give these a try and let you know!

Thanks again
Alun