BACKSTORY IN CASE YOU NEED IT. I am working on a spreadsheet I created to tally up payroll. I am great at creating spreadsheets. Pretty good at knowing formula options for most needs. BUT I am sadly VERY bad at math!! So I often blow it when I create a formula to complete a task. It properly achieves what I tell it to do -- but what I tell it is often incorrect math and I end up overpaying employees! Today's loss was about $500 Total (ouch!) so I need to fix this!!
NEED. I need to take the sum of 2 cells [A & B] and then calculate correct OT & separately cacluate DT in two different solution cells [C & D]. FYI in case it matters -- in CA, OT/DT is NOT calculated as a simple 1.5x or 2x rate of the normal rate of pay, but calculated against a separate rate called "Regular Rate of Pay" which varies per employee week by week. So I already have 2 other cells [E & F] set up as reference cells to pull from that has those 2 rates listed: Normal Pay Rate [E] and Weekly Reg Rate of Pay [F].
I need these solution cells [C & D] to be a dollar amount. Not a QTY of hours. But the final dollar amount of the QTY of hours x the applicable pay rate, in this case, Cell F.
WHERE I STRUGGLE IS: Getting the formula for cells [C & D] to pull out any potential ISOLATED hours of any hours between 8-12 [Cell C] and any potential ISOLATED hours of 12+ [Cell D]. AND --- because those will sometimes be ZERO hours -- I don't want the solution of [C & D] to ever be LESS THAN $0.00.
WHEW! HOPE THIS IS MAKING SENSE!!
Cell A: Total daytime hours worked Cell B Total nighttime hours worked
STEP ONE: Need to add those 2 QTY hours together
STEP TWO to get to the solution for [Cell C]: (somehow!) Isolate & pull out JUST those 4 potential hours of THAT Sum being possibly more than 8 and less than 12.
STEP THREE: Multiple THAT sum against [Cell F] = the final solution of $ for [Cell C] = the amount of additional OT pay due.
STEP FOUR: Ensure THAT sum from Step There is never less than 0 Zero.
THEN: Repeat this basic concept for [Cell D] but this time, it would be ONLY for any potential QTY of hours beyond 12+ multipled by [Cell D].
NOTE: this needs to be JUST the OT or DT $ (on top of their standard baseline pay rate). The ISOLATED "extra" of 1/2x and 1x Pay amount. FYI -- The baseline Sum of [Cell A & B] are already being calculated in a different cell. Remember = in CA , all baseline hours are first calculated against a "Standard Pay" and then whatever extra QTY beyond 8 is calculated at a different "Regular Rate of Pay" (ugh! So tedious! Don't move to CA if you are an employer!)
It is easier if you put your sample data into a small Excel file and upload that, along with some rules as to how we should calculate the # of hours (it is not clear to me how you arrived at the 4 hrs and 1.5 hrs in your example above, starting from the 9.25 and 4.25 hours in A and B)