Circular Reference Error

Hello,

I am at a full standstill trying to get the right functions to operate.
Here are the parameters of the time sheet that I'm trying to fit.

-8 hour work day (REG), anything past 8 REG = overtime OT,
-overtime (or a combination of REG and OT totaling 12) past 12 hours = Double time DT
-40 hour work week, ANY hours worked past 40 go directly to the overtime column

I am trying to make column I (REG) display totals only up to 40. I9 to I15= 39.25, so I16 should equal 0.75, J16 should add 7.25 and I17 should be 0/blank and the total of I17 (8) added to J17 (4).

The problem i'm hitting is that when I make an IF statement reacting to the 40 hour week overflow in column I, it affects all the items in that column with the formula. Is there a better way to do a sequential sum to 40 in column I (REG) with overflow in column J (OT) PLUS a combined row sum of each I + J cells totaling 12 and the overflow past 12 (of I + J cells in each row) to add to column K (DT).

If you can mention to me a function that can do this let me know, else I have to look at how to script it with the VBA with my amateur knowledge.

ts.png

Few questions.

1. Each row represents single day, correct?
2. So the worksheet is for 3 week period.

Let me see if I understood the business rule...
Lets say employee worked total of 45 hours in a week M-F.
M = 9
T = 14
W = 8
Th = 8
F = 6

Regular Pay = 8 + 8 + 8 + 8 + 1 = 33
Total OT = 1 + 4 + 5 = 10
Total DT = 2

Is that right?

Are you opposed to changing worksheet structure a bit?

Ok...

So, if I understood it right, you'd need helper column (to avoid long formula or circular reference).

See attached set up. I've put helper column and hidden it (column I).

It worked for few tests that I did. But you should test thoroughly. Let me know if you find issues.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)