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).


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Emp[/TD]
[TD="align: center"]EH From[/TD]
[TD="align: center"]EH To[/TD]
[TD="align: center"]Subtotal[/TD]
[TD="align: center"]3 HR Incentive[/TD]
[TD="align: center"]AM Incentive[/TD]
[TD="align: center"]PM Incentive[/TD]
[/TR]
[TR]
[TD]Last, First[/TD]
[TD="align: center"]XXXX[/TD]
[TD="align: center"]1830[/TD]
[TD="align: center"]2200[/TD]
[TD="align: center"]3:30[/TD]
[TD="align: center"]-0.50[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

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.