Formula Help....Is this even possible?

[MightyMach]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]BG[/TD]
[TD]BH[/TD]
[TD]BI[/TD]
[TD]BJ[/TD]
[TD]BK[/TD]
[TD]BL[/TD]
[TD]BM[/TD]
[TD]BN[/TD]
[TD]BO[/TD]
[TD]BP[/TD]
[TD]BQ[/TD]
[TD]BR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Hrs Sub[/TD]
[TD]Rate Sub[/TD]
[TD]Total Sub[/TD]
[TD]Hrs Rev[/TD]
[TD]Rate Rev[/TD]
[TD]Total Rev[/TD]
[TD]Hrs Sub[/TD]
[TD]Rate Sub[/TD]
[TD]Total Sub[/TD]
[TD]Hrs Rev[/TD]
[TD]Rate Rev[/TD]
[TD]Total Rev[/TD]
[/TR]
[TR]
[TD]ST[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]100[/TD]
[TD]800[/TD]
[TD]12[/TD]
[TD]100[/TD]
[TD]1200[/TD]
[TD]8[/TD]
[TD]100[/TD]
[TD]800[/TD]
[TD]10[/TD]
[TD]100[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]OT[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]150[/TD]
[TD]600[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]150[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DT[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So I have 11 days set up like the above. The contractor submits his bill which is inputted into the "Sub" columns. When I receive his time cards, I add his total hours into the "REV" columns. As you can see in this example, the contractor submitted for OT after 8 hours despite state law mandating OT after 40 hours only. I would like to write a formula that captures this difference.

The Formula I wrote goes like this: =if(and((BG4:BG6)=(BN4:BN6),BG4<>BN6,BL4-BI4)+If(and((BM4:BM6)=(BP4:BP6),BM4<>BP4,BR4-BO4). However, it's not working consistently. Any Help is appreciated!!

What I am trying to get is: "If all submitted hours on Monday equal all reviewed hours on Monday, and Straight time hours on Monday do NOT equal reviewed Straight time hours on Monday, then Monday Reviewed Straight time total minus submitted Monday straight time total."

From there, I want to add the same formula to all days and add them all together.

 

[James006]

Hello,

Not sure to fully understand your requirement ... and your expected result ...:wink:

In cell BL4 ... (my guess...) you could test following:

=IF(AND(BH4+BH5=BK4,BH4<>BK4),(BK4*BL4)-BJ4,BK4*BL4)

Hope this will help