IF, THEN for time range formula assistance

[JamesTirone]

Hello, I scoured the forums for a scenario like mine so I didnt post something redundant, but found nothing.

please let me know if anyone can help with this:

goal is to "score" call center stats which are always time format. I cannot get my "IF" statement to work with time. For example, =IF(C66>:59, "STRETCH") (I could not get the formula to understand that "1:00" means one minute, not one oclock. for example, 1:04 is one minute, four seconds. Excel can understand 00:00:00 format, but I dont know how to put the formula into it. 

here is my goal, if you can follow:

=IF(C66 (is under) one minute, "STRETCH", IF(C66 (is between) 1:01-1:15, "TARGET", IF(C66 (is between) 1:15-1:30, "LOW TARGET", =IF(C66>1:30, "INPROFFICIENT")

If the in-betweens are too complex, I would be fine with "if under 1 minute, Stretch, if under 1:15, Target, if under 1:30, low target, and if over 1:30, inproficient". actually come to think of it, It doesnt matter to me if excel thinks this is time or minutes and seconds, because 2:05 and 2 minutes 5 seconds can still be marked according to my ratings regardless of format in this case.

thank you for your time and help!

ps- here are some i tried which failed:

(IF(C64<00:01:00, "STRETCH")

=(IF(C64<:01:00, "STRETCH")

=(IF(C64<:59:, "STRETCH")

=IF(C64<60, "STRETCH")- works, but not how I want it to

=(IF(C65>1:30,"INPROFICIENT"))- this gives me "SPILL"

=(IF(C65>1,"STRETCH",IF(C65<2,"INPROFICIENT"))) works, but not in the way I want it to 

=IF(C66>:01:30, "INPROFICIENT")- failed

thank you again!

[#2]

@JamesTirone

Excel expresses time as a decimal value where 1 day = 1, so 1 minute would be 1/1440 or 0.00069444. You can see this if you select cell C66, select format cell and switch it to general (switch back afterwards because it isn't very readable that way).

For your formula try using =IF(C66>1/1440, "STRETCH") instead, this would be 1 minute. If you want to do this by seconds, try =IF(C66>59/8640, "STRETCH") for 59 seconds.  

[#3]

@JamesTirone 

It's better not to hardcode constants within the formulas, but keep them as parameters in helper ranges at any place of your workbook.

Here

it's in E1:F4

With that formula could be

=LOOKUP(A1,$E$1:$E$4,$F$1:$F$4)

If you still prefer nested IF or like, you may use TIME() function not to recalculate number of minutes to numbers. E.g. 00:01:30 is returned by TIME(0,1,30)