Help With a Formula

[MSF]

Hello!

Given this formula:

=IF(OR(HOUR(AN2)+INT(AN2)*24 < 120, AND(HOUR(AN2)+INT(AN2)*24 = 0, MINUTE(AN2)+INT(AN2)*60 = 0, SECOND(AN2)+INT(AN2)*60 < 60)), IF(((100*(AN2/$AP$2))-100)/100 <> -1, ((100*(AN2/$AP$2))-100)/100, 0), "INVALID")

Essentially, if the time entered is 120 hours or greater, or is 59 seconds or less, the cell should display "INVALID".

But a time of 0:00:02 is not displaying "INVALID".

I can't seem to figure out why. Any suggestions?

 

[JamesCanale]

MrExcelPlayground15.xlsx
	G	H
18	121:00:00	INVALID
19	10:00:00
20	0:00:58	INVALID
21	0:01:00
Sheet9

Cell Formulas
Range	Formula
H18:H21	H18	=IF(OR(G18>5,G18<1/60/24),"INVALID","")

"5" is 5 days, or 120 hours. the other one is a minute.

 

[aaewalsh]

Here is the correct formula

=IF(OR(HOUR(AN2)+INT(AN2)*24 >= 120, SECOND(AN2)+INT(AN2)*60 <= 59), "INVALID", 
 IF(((100*(AN2/$AP$2))-100)/100 <> -1, ((100*(AN2/$AP$2))-100)/100, 0))

This formula should correctly check whether the time entered in cell AN2 is greater than or equal to 120 hours, or less than or equal to 59 seconds, and if so, display "INVALID", otherwise it will calculate the value based on the ratio of AN2 to the value in cell AP2.

 

[MSF]

Thanks for the help! Unfortunately, my own lack of attention and idiocy was the problem - the OR should have been AND.