This doesn't sound hard, but maybe I'm missing something.
I started with this table:
For ease, I added a column D that calculates the number of hours worked - a simple calculation of C2 - B2. If you don't want this, you can incorporate the formula in the category calculations, but it's your call.
Either way, this cell needs to be set as a Duration type.
For Ordinary Hours (E2), you need the MINimum of hours worked or 8 (i.e. if Hours Worked is greater than 8, then log 8, otherwise log the hours worked). Therefore, E2=
=MIN("8h",D2)
For the 1.5x hours (F2), you need to find the minimum of 3 (the most number of hours billable at the 1.5x rate) and the number of hours > 8, so F2=
=MIN("3h",D2−E2)
D2 -E2 relates to the amount of overtime (hours worked minus ordinary hours), capped at 3
Similarly, 2x rate (G2) calculates the hours worked minus regular hours and minus 1.5x. hours, capped at 13 hours (can't have more than 13 hours at 2x rate since there are 24 hours in a day, and the first 11 are a lower rate). Therefore G2=
=MIN("13h",D2−E2−F2)
