Need Help to Calculate the Point Based on Break Time

[Sanjibghosh]

Hi All,

I want to calculate the point based on break time. I am using this formula

Please Login or Register  to view this content.

But it's showing wrong answer 3 instead of 1.5

Point criteria on Break Time are

>1:00 = -.5, >=1:10= - 1, >1:15= -1.5

[samba_ravi]

=IF(A2>=TIME(1,15,0),(3-1.5),IF(A2>=TIME(1,10,0),(3-1),IF(A2>TIME(1,0,0),(3-0.5),3)))
try this

[Phuocam]

Or try this ...

=3-LOOKUP(A2*1440,{0,61,70,76},{0,0.5,1,1.5})