California hourly employee time calculator

I am trying to build the perfect California hourly employee time calculator within excel. I think I'm almost there - but need a little assistance with a few formulas.

1) I need to have my OT formula modified to cap out at 4 hours of OT in any day. Currently my formula is: =IF(((C7-B7)+(E7-D7))*24>8,((C7-B7)+(E7-D7))*24-8,0)... but again - I need this to cap out at 4.

2) I would like to have the meal break formula modified. Currently my formula is: =IF(((C8-B8)*24)>5,1,0)... I need to add an exception that if ((C7-B7)+(E7-D7))*24 is less than or equal to six, then the result is zero.


There are other odd criteria - that would apply only to the row of totals - so I will work on that separately since those instances rarely arise.

Any help you can provide on these two formulas though would be extremely helpful. A copy of the excel form is attached...

Thanks!!!

Use this formula in G7:

Formula:

Please Login or Register  to view this content.

Then put this formula in H7:

Formula:

Please Login or Register  to view this content.

and this one in I7:

Formula:

Please Login or Register  to view this content.

then copy down.

Hope this helps.

Pete