Subtract break > or < certain number of hours worked

[Jabsrocha]

Good evening all,

I've taken some excellent examples already from you forum, but now I'm struggling with a more complicated one which I can't find an answer for.

To complete my timesheet.
If I work less than 8 hours no break is discounted.
Between 8 and 10 hours, 30 min. are discounted.
More than 10 hours, 1 hour is discounted.

I have this so far, and it works only for "greater than".

=IF(OR(ISBLANK(E32),ISBLANK(D32)),"",E32-D32-(E32-D32>TIME(8,0,0))*TIME(0,30,0))


I don't know what to do for the rest.
Please help.
Thanks

 

[Jabsrocha]

I forgot to mention that the only 2 fields I fill out, are the start and end time.

 

[Aladin Akyurek]

Either use the following Table in the formula...

=E32-D32-TIME(0,LOOKUP(E32-D32,INDEX(Table,0,1),INDEX(Table,0,2)),0)

Or the formula with a hard-coded table:

=E32-D32-TIME(0,LOOKUP(E32-D32,{0;0.333344907407407;0.416678240740741},{0;30;60}),0)

 

[jtakw]

Hi,

I might be misunderstanding, but I think Aladin needs a slight adjustment to his formula to include discounting 8 hours by 30 minutes:

I've also provided a couple of alternatives:

Book1
	A	B	C	D	E
28	Net Hours	Net Hours	Net Hours
29	1 way	Another way	Aladins
30	8:30	8:30	8:30
31	7:30	7:30	8:00	Start Time	End Time
32	7:30	7:30	7:30	9:00 AM	6:00 PM
33	10:00	10:00	10:00	9:00 AM	5:00 PM
34	9:30	9:30	9:30	9:00 AM	4:30 PM
35				9:00 AM	8:00 PM
36				9:00 AM	7:00 PM
Sheet1

Cell Formulas
Range	Formula
A30	=IF(OR(ISBLANK(E32),ISBLANK(D32)),"",(E32-D32)*24-IF((E32-D32)*24>10,1,IF((E32-D32)*24>=8,0.5,0)))/24
B30	=IF(OR(ISBLANK(E32),ISBLANK(D32)),"",E32-D32-IF(E32-D32>TIME(10,0,0),TIME(1,0,0),IF(E32-D32>=TIME(8,0,0),TIME(0,30,0),0)))
C30	=E32-D32-TIME(0,LOOKUP(E32-D32,{0,0.333344907407407,0.416678240740741},{0,30,60}),0)

 

[Jabsrocha]

Thank you both for you help.
I'm going with suggestion in B30, but with a small modification.

=IF(OR(ISBLANK(E32),ISBLANK(D32)),"",E32-D32-IF(E32-D32>=TIME(10,0,0),TIME(1,0,0),IF(E32-D32>=TIME(8,0,0),TIME(0,30,0))))

">=" In both time slots and I deleted the remaining ",0" which doesn't affect the outcome, I think?

Thanks again

 

[jtakw]

If I work less than 8 hours no break is discounted.
Between 8 and 10 hours, 30 min. are discounted.
More than 10 hours, 1 hour is discounted.

You're welcome, welcome to the forum.

If you use ">=" in the first TIME test, that would include discounting 1 hour instead of 30 minutes for 10 hours worked which contradicts your description in your OP, but if that's what you meant, then you're right to add the =.

Also, you're correct, the last 0 doesn't affect the outcome, I put it in just to satisfy the IF formula syntax (Value if false).

 

[Aladin Akyurek]

Either use the following Table in the formula...

=E32-D32-TIME(0,LOOKUP(E32-D32,INDEX(Table,0,1),INDEX(Table,0,2)),0)

Or the formula with a hard-coded table:

=E32-D32-TIME(0,LOOKUP(E32-D32,{0;0.333344907407407;0.416678240740741},{0;30;60}),0)

I see Table is not there...

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]0:00:00[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8:00:01[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10:00:01[/TD]
[TD="bgcolor: transparent, align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

You can add...

=IF(D32*E32,X,"")

where X is one of the suggested formulas.