nested function

[Marcel]

Good morning all!
I've run into all kind of problem with my work sheet, here they are:

Rows are 15 to 21
A B C D
E F Saturday 8.00 20.00 12.00 -8.00 4.00
Sunday 8.00 20.00 12.00 -8.00 4.00
Monday 6.50 15.50 9.00 -8.00 1.00
Tuesday 11.50 20.50 9.00 -2.00 7.00
Wednesday 11.50 20.50 9.00 0.00 9.00
Thursday 11.50 20.50 9.00 0.00 9.00
Friday 11.50 20.50 9.00 0.00 9.00

the formula/function i have problem with are in Cell E15 to E 21. here's the
formu la i have in E18
(=IF(SUMIF($E$15:E18,">0",$E$15:E18)>40,40-SUM($E$15:E18),-8). what i'm
trying to achieve is when ever the sum of E reach that day and the total is
40 + i need to have E18 reflex the fifference of hour ie example.

when i run into problem is when i take the saturday hours off i get this
A B C D
E F
Saturday 0.00 -8.00 -8.00
Sunday 8.00 20.00 12.00 -8.00 4.00
Monday 6.50 15.50 9.00 -8.00 1.00
Tuesday 11.50 20.50 9.00 -8.00 1.00
Wednesday 11.50 20.50 9.00 -8.00 1.00
Thursday 11.50 20.50 9.00 -8.00 1.00
Friday 11.50 20.50 9.00 -17.00 -8.00

My formulas/function in cells are:
F19 -
=IF(F18>-8,0,IF(SUMIF($E$15:E19,">0",$E$15:E19)>40,40-SUMIF($E$15:E19,">0",$E$15:E19),-8))

F20 -
=IF(F19>-8,0,IF(SUMIF($E$15:E20,">0",$E$15:E20)>40,40-SUMIF($E$15:E20,">0",$E$15:E20),-8))

F21 -
=IF(F20>-8,0,IF(SUMIF($E$15:E21,">0",$E$15:E21)>40,40-SUMIF($E$15:E21,">0",$E$15:E21),-8))

CAN ANYONE HELP ME

 

[JLatham]

Marcel,
Can you give us an example using the values in A through D and show us what
you expect to see in columns E and F? I think seeing what you are trying to
get will help with a result.

Also, please tell us which cell the first formula you show is really in.
You said it is in E18, but I think that is wrong, since if it were in E18,
you'd get a circular reference error -- unless that is part of the problem
you are having. Maybe it is in E19??

 

[JLatham]

I *think* that your problem may be solved by using a simpler function: MIN()

Let's try this and see where it takes us to begin with - but do post with
answer to my earlier question also.

In E15 put
=MIN(SUM(B1515),40)
in F15 put
=SUM(B1515)-E15

and fill those formulas on down through row 21. Does that give what you
desire?
You might try changing the ,40) to ,32) if a normal workweek is 32 hours
and not 40?

 

[Marcel]

The copy and paste didn't come out right the column i work with is F

i'll try to explain, it would be easier to send it ...lol. here we go

A15 = is my Day of the week = Saturday
C15 = Start Time = 8.00
D15 = End of Day = 17.00
E15 = Total Hours worked = 9.00
F15 = Regular hour = -8.00
G15 = Hrs in Excess = 1.00
H15 = Toil earned = 1.50
and so on until i reach Friday on row 21.

In E15 I have =D15-C15
the formula in H15 is Irrelevant

In F15 i'm tryin to have that -8.00 change as the week goes on.
ie. when the employee reach 40 hrs (we work 40 a week here) the -8.00 react
to the total.
let say on tuesday row 18, we reach 45 hrs, the number in F18 should schange
to -5.00, cause he is 5 hrs past the 45 hrs G18 should give me 5.00 and F18
should read -5.00 (45 - 5 = 5, somehow). at the same time cause we are over
40 hrs in hrs work, F19,20,21 should go to 0.00. and so on depending when the
employee reach 40 hours.
Thanks
Marcel

 

[JLatham]

Marcel, it appears to me that you have these rules for what are normal hours
(column F) and what are Excess/premium/overtime hours:
any work over 8 hours per day is Excess
any work over 40 hours per week is Excess

I believe these formulas in column F will perform the task required:
in F15, use this formula:
=MIN(8,E15)
in F16, use this formula:

=IF(SUM(E$15:E15)>=40,0,IF(SUM(E$15:E16)<40,MIN(E16,8),MIN(8,40-SUM(E$15:E15))))

Then 'fill' the formula from F16 on down through F21.

In G15 put this formula and fill it on down through G21:
=MAX(0,E15-F15)

This gives me the following information in the indicated rows/columns:
Row C D E F G
15 8.0 20.0 12.0 8.0 4.0
16 8.0 20.0 12.0 8.0 4.0
17 6.5 15.5 9.0 8.0 1.0
18 11.5 20.5 9.0 7.0 2.0
19 11.5 20.5 9.0 0.0 9.0
20 11.5 20.5 9.0 0.0 9.0
21 11.5 20.5 9.0 0.0 9.0

I hope this is what you need and helps you.