How to use time in an IF formula

[kwalker4]

Good morning.

I'm having trouble using time (minutes and seconds) in an IF formula. I'm
trying to assign a rating to four time thresholds. My rating and thresholds
are as follows...
"EE" <0:45 sec
"MA" 0:45-1:00
"MM" 1:01-1:30
"DNM" >1:31
For example, if cell P7 is 0:42 then cell Q7 would be assigned a rating of
"EE". If P7 is 1:31 then "DNM". Please tell me what my IF formula should be.
I have tried everything and nothing has worked so far. I appreciate your
help. Thanks!

 

[Sean Timmons]

That depends on what you have in the cells. Are they truly set as seconds or
are they minutes?

a day equals 1. So, an hour is 1/24. A minute is 1/1440. a second is 1/86400

sooo.. f they are set as true seconds, it would be..

if(P7< 45/86400,"EE",if(P7< 60/86400,"MA",if(P7< 80/86400,"MM","DNM")))

else, change to 1440 instead of 86400 above.....

 

[Sean Timmons]

That depends on what you have in the cells. Are they truly set as seconds or
are they minutes?

a day equals 1. So, an hour is 1/24. A minute is 1/1440. a second is 1/86400

sooo.. f they are set as true seconds, it would be..

if(P7< 45/86400,"EE",if(P7< 60/86400,"MA",if(P7< 80/86400,"MM","DNM")))

else, change to 1440 instead of 86400 above.....

 

[Jacob Skaria]

Try the below with A1 in time format

=LOOKUP(MINUTE(A1)*60+SECOND(A1),{0,45,61,91},{"EE","MA","MM","DNM"})

If this post helps click Yes

 

[Jacob Skaria]

Try the below with A1 in time format

=LOOKUP(MINUTE(A1)*60+SECOND(A1),{0,45,61,91},{"EE","MA","MM","DNM"})

If this post helps click Yes

 

[kwalker4]

Thanks for the reply. When I look at the the cell it is formated by time. So
in P7 it shows 1:19 which I read as one minute nineteen seconds as I entered
it, but it is really 1:19:00 AM. How do I fix this? Thanks!!!

 

[kwalker4]

Thanks for the reply. When I look at the the cell it is formated by time. So
in P7 it shows 1:19 which I read as one minute nineteen seconds as I entered
it, but it is really 1:19:00 AM. How do I fix this? Thanks!!!

 

[David Biddulph]

If you enter it as 1:19 it will be treated as 1:19:0.
If you want 0:1:19 you need to enter it as 0:1:19 or as 1:19.0
If you want to change existing data, put 60 in a spare cell, copy it, then
select the data that you want to compare, and Edit/ Paste Special/ Divide

 

[David Biddulph]

If you enter it as 1:19 it will be treated as 1:19:0.
If you want 0:1:19 you need to enter it as 0:1:19 or as 1:19.0
If you want to change existing data, put 60 in a spare cell, copy it, then
select the data that you want to compare, and Edit/ Paste Special/ Divide