Complicated Time Sheet for End User

Hello there,

I need to create a time sheet for a Union Crew Coordinator to use to enter his crew's time. There are several rules and I do not know where to begin.

Call Types:
1. Performance Call - 4 Hours Max at Performance Rate of A
2. Rehearsal Call - 4 Hours Max at Rehearsal Rate of B
3. Work Call - Minimum Work Call is 3 Hours [so if only worked 1 Hour; receives 2 hours of continuity pay] and at Rate C
4 Rigging Call - Rate D

Early Call Rule: Time and a half (1.5x) for any work performed from 12:01 AM to 8:00 AM. Reverts to straight pay after 8:00 AM.
Night Rule: Time and a half (1.5x) for any work performed after 12:00 AM into the next day
Overtime Rule: Time and a half (1.5x) for any work performed over 8 hours in the same day (excludes Performance Calls and Rehearsal Calls)

Essentially...I had created a data validation list of the call types with the rates associated with them in the cells next to them. I started doing and if and statement...however need advice on how to correctly calculate hours with rules said above. Here is a sample of my formula:

=IF(AND(C6="Work Call",D6>TIME(7,59,0)),((E6-D6)*24),((E6-(TIME(8,0,0)))*24))

I need to be able to calculate this on a scale greater than 24 hours as work that continues past midnight will be recorded on that days timecard.

Let me know if you have any advice! Thanks.

i hate these things.
you have to build an employee sheet. With all their rates vs Hr Max
(i then load them all into memory so I dont have to lookup sheets)

then you have to sort the data by person/date
scan the list 1 at a time
validating each record with the person's CallType vs HrsTotal, then determine the rate.
create extra time record when they reach the end of RegTime, and begin OT.
next record

uhg.