How to calculate hours worked to trigger a cost

Hi,

I have a huge task of calculating the time that staff have worked to trigger off a cost.

If a Field Officer works 7am till 3pm on Saturday there are two different costs I need to calculate to trigger an invoice.

7am till 8am is £22.86per hour and then 8am till 3pm is £16.87.

There are different costs depending on the day of the week worked and the position they hold but if I can get a formula to help me calculate the split in time to provide me with a cost I can work the rest out.

Please advise?

I've attached a sample document for ease.

Thank you.

You might try this:

Formula:

Please Login or Register  to view this content.

If you had a table of pay rates, you could use an index match - which would be easier to update in formulas if the rate ever changes.