Annoying Timesheet Problem - Maximum Hours

Hi, I am developing a timesheet in Excel that will allow users to enter their time against an activity. Simple enough you would think, however it must operate under the following rules:

1. the maximum time permitted for any single day is 8 hours - any time spent over and above that on any one day is not paid.

2. People can charge their time in against multiple roles, which are paid at different rates. If you exceed a total of 8 hours on any single day across two or more roles, the higher rate takes priority with the time spent in lower rates being used to "top up" the balance to a maximum of 8 hours.

I've solved (1) - simple formula that adds up the cumulative balance of work performed on a particular day and tells the cell calculating the value of the time spent to return nil.

2 is where things get sticky. For example

Date Role Rate Hours Total Payable
8 Feb Role 1 $100 6 $600
8 Feb Role 2 $50 4 $200
TOTAL for 8 FEB 10 $800

The total that should be paid is 6 Hours at $100, and then a further 2 hours at $50. The spreadsheet should return $100 in the value to be paid in the second row instead of $200.

And to make matters even more complicated, on the existing manual timesheets we use people are not entering time in chronoligical order - so in any row (there are a maximum of 65) you could have any date and any role.

Thought about including a validation macro which creates a separate summary sheet and applies the necessary business rules to the daily totals. But that would take me a bit of time and I was hoping there would be a more simple formula solution.

Any help is much appreciated.