nested function
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(B15:D15),40)
in F15 put
=SUM(B15:D15)-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" wrote:
Good morning all!
I've run into all kind of problem with my work sheet, here they a
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 a
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
|