Google Sheets hours worked before and after specified time

Question

I am trying to specify a time that starts a shift premium for a time card. I was so happy I finally figured out how to get it, but then I changed a time to a first shift time and thats when I lost all hope. I have spent hours messing with this and my current formula's looks like

x1.05

 =IF(COUNT(A20:B20)=2,MOD($B$18-A20,1)*24,"")

x1.1

 =IF(COUNT(A20:B20)=2,MOD(B20-$B$18,1)*24,"")

My end goal is to have it so first shift (7am - 3pm) fall under base pay, 2nd shift (4pm-12am) fall under x1.05 and anything past 12am falls under x1.1

Just getting this far has been a mind blower for me and any help would be greatly appreciated.

I have moved this to its own sheet to share with everyone. I am still playing with things myself and trying different things, but on the sheet I have included the variables along with the wording of the contract that effects the pay scale. NOTE the times I originally provided were just examples from the top of my head.

My Sheet

Answer 1

It doesn't look like your current sheet accounts for the multiple criteria you're wanting to evaluate. First shift (x1) begins at 7; second shift (x1.05) begins at 15:00, and third shift (x1.10) begins at 24:00.

I added each of those directly above the columns they affect and used this formula for the Base column:

=IF(COUNT($A20:$B20)=2,IF($A20<F$18,(MOD(MIN(F$18,$B20)-$A20,1))*24,0),"")

...this for the x1.05 column:

=IF(COUNT($A20:$B20)=2,IF($A20<G$18,(MOD(MIN(G$18,$B20)-$A20,1)*24)-E20,0),"")

...and this for the x1.10 column:

=IF(COUNT($A20:$B20)=2,(MOD(MIN(E$18+1,$B20)-$A20,1)*24)-SUM(E20:F20),"")

So far, it's working as expected. One thing I didn't add into it is to account for someone who starts their shift before 7 a.m. If you want this to be included in the x1.10 column, you could add a calculation for that to the formula there.

Here's what it looks like:

I'm working in Excel, but these formulas should all work in Google Sheets as well.