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

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??

"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

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

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" wrote:

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??

"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

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(E1 6,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.



"Marcel" wrote:

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" wrote:

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??

"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