Hourly computations that change what displays after 40 hours are met

I don't even know how to ask this question, so I might have to draw it for you. Gah!! Without being able to point and show you what I mean, this is hard.

Here is what I have:

a list of hours in cells A1:I1 on a sheet named Time

Time

	A	B	C	D	E	F	G	H	I
1	Date	Name	12	12	12	10	10	5	0

\Time/\Sheet2/

On a separate page, I need it to calculate the total hours, splitting them up into regular and overtime per day- easy enough. BUT overtime is reached once you hit 40.

What the separate sheet (let's name it Sheet2) would look like this

Sheet2

	A	B	C	D	E	F	G	H	I
1				6	10	5
2	12	12	12	4

\Time/\Sheet2/

Since no one will work over 12 hours a day typically, we can start this overtime issue in D1

In the overtime part I tried a variance of this: =IF(SUM(Time!C2+Time!D2+Time!E2-40>0),SUM(Time!C2+Time!D2+Time!E2-40,"") which is fine, I think, but what the heck would I put in cell Sheet2!D2

I tried =Time!F2-D1, which gives the right number, 4, but what if the sum of A2:D2 was only at 30 hours so far? Then it would not have worked. AAAAAAH



Help. If this is a payment type thing, I can repost in the commercial part. I did not know if I have just been looking at it too long and am missing a simple IF - THEN - MATCH - whatever statement.


Yuk.

I posted here a while ago for a time sheet that is BRILLIANT and this is actually going to be a page into that time sheet. I had someone offer me $100 to buy it from me and I said NO! Hahaha.

Thanks, all you smarties.

Brandi

Hi Brandi,

I used the below formula for the "normal hours worked":

=IF(COLUMN(A2)=1,Time!C1,MAX(MIN(40-SUM(Time!B1:$C1),Time!C1),0))

This was entered in A2 on sheet2 and then dragged it across for the other days in the week.

For the overtime I put "=Time!C1-A2" into A1 and dragged it across again.

Hope this works for you.

OMG That formula makes my brain hurt. Lemme try it.