Timesheet - Hour +/-

[JAGTOO]

Hi i have this table for a timesheet with the following formula:

	H	I		J		K
Day	In	Out	Formula OT 25%	OT 25%	Formula OT 50%	OT 50%
1	8	17	=SI(I3>=19.1,"3",(I3-H3-9))	0	=(I3-H3-9-J3)	0
2	8	19	=SI(I3>=19.1,"3",(I3-H3-9))	2	=(I3-H3-9-J3)	0
3	8	21	=SI(I3>=19.1,"3",(I3-H3-9))	2	=(I3-H3-9-J3)	2
4	8	16	=SI(I3>=19.1,"3",(I3-H3-9))	-1	=(I3-H3-9-J3)	0
5	8	18.5	=SI(I3>=19.1,"3",(I3-H3-9))	1.5	=(I3-H3-9-J3)	0
			Total HE 25%	5.5	Total HE 50%	2

<tbody>
</tbody>

It worked fine because it didn´t work with hour format, but know they want something more complex. I don't have much expirience with Excel and I've been trying to change the formula to reflect hour format with no results please help. The table should look something like this.

	H	I		J		K
Day	In	Out	Formula OT 25%	OT 25%	Formula OT 50%	OT 50%
1	8:00	17:00		0		0
2	8:00	19:00		2		0
3	8:00	21:00		2		2
4	8:00	16:00		-1		0
5	8:00	18:30		1:30		0
6	8:15	17:00		-0:15		0
7	8:40	17:00		-0:40		0
8	8:00	22:20		2		3:20
			Total OT 25%	7:30	Total OT 50%	5:20
			Total T Deduct	1:55

<tbody>
</tbody>

Total OT 25% Should only reflect the positive values in colum J
Total OT 50% Should also refelect the positive values in colum K
Total T Deduct Should only reflect the negative values in colum J

Thanks in advance for your help!!

 

[Special-K99]

You need to explain the rules for overtime.

It looks like (correct me if Im wrong - thats why you need to explain it)

the working day is 0800-1700
The first 2 hours are paid at 25%
Any hours over that time are paid at 50%

However, your formula for Day 3 looks wrong
In time is 8 Out time is 21, that's greater than 19.1 so it should return 3 but your values are 2 and 2 in columns J and K ??????


Is this correct?