Formula not working when calculating time

[MrsAndyTaylor]

Hello!

I have a worksheet that has a list of times people are picking up extra hours. They get an incentive (-.50) if they work any three hours on Sunday(-.50); they get another incentive if they work between 0900-1300 (-.50), and/or 1830-2300 (-.50). The formula I have only works if they pick up exactly 0900-1300, or 1830-2300. So, if someone works 0900-1200 (3 hours), or 1800-2200 (4 hours) the formula isn't giving them the (-.50).

		D	E	F	H	I	J
Name	Emp	EH From	EH To	Subtotal	3 HR Incentive	AM Incentive	PM Incentive
Last, First	XXXX	1830	2200	3:30	-0.50	0.00	0.00

<tbody>
</tbody>

Column H formula [=IF(($E2-$D2)>=0.125,-0.5,0)]
Column I formula [=IF($D2<=TIME(9,0,0),IF($E2>=TIME(13,0,0),-0.5,0),0)]
Column J Formula [=IF($D2<=TIME(9,0,0),IF($E2>=TIME(13,0,0),-0.5,0),0)]

 

[The_Macrotect]

Hi Andy,

Assuming employees can get both AM and PM incentives, try this:

Column H:

=IF(F2-E2>=300,0.5,"")

Column I:

=IF(OR(901=MEDIAN(E2:F2,901),1299=MEDIAN(E2:F2,1299)),0.5,"")

Column J:

=IF(OR(1831=MEDIAN(E2:F2,1831),2299=MEDIAN(E2:F2,2299)),0.5,"")

Does this do the trick?


All the best,
Matt

 

[MrsAndyTaylor]

No, that didn't work.

 

[steve the fish]

Your first sentence mentions sunday. There is no reference to date on your example.

 

[MrsAndyTaylor]

It's only tracking the extra hours for yesterday, and no other days.

 

[Marcelo Branco]

Maybe...

I2
=IF(MIN(TIME(13,0,0),E2)-MAX(TIME(9,0,0),D2)>=0.125,-0.5,0)

J2
=IF(MIN(TIME(22,0,0),E2)-MAX(TIME(18,30,0),D2)>=0.125,-0.5,0)

Hope this helps

M.

 

[MrsAndyTaylor]

Maybe...

I2
=IF(MIN(TIME(13,0,0),E2)-MAX(TIME(9,0,0),D2)>=0.125,-0.5,0)

J2
=IF(MIN(TIME(22,0,0),E2)-MAX(TIME(18,30,0),D2)>=0.125,-0.5,0)

Hope this helps

M.

That worked!! Thank you.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.