Hi,
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!)
HERE IS AN EXAMPLE OF WHAT I NEED TO ACCOMPLISH.
Cell A: 9.25
Cell B: 4.25
Cell E: $20
Cell F: $21.10
Cell C: In this example, the solution should end up being $42.20, which is 4 Hrs x $21.10 x .5. (= to Cell F x 1/2)
Cell D: In this example, the solution should end up being $31.65, which is 1.5 Hrs x $21.10 (= to Cell F x 1)
HOPE THIS MAKES SENSE!
HOPE YOU CAN HELP!
THANKS! -- Janice
