How to use time in an IF formula
 

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!

How to use time in an IF formula
 

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

"kwalker4" wrote:

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!

How to use time in an IF formula
 

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


"kwalker4" wrote:

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!

How to use time in an IF formula
 

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!!!

"Sean Timmons" wrote:

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

"kwalker4" wrote:

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!

How to use time in an IF formula
 

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

"kwalker4" wrote in message
...
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!!!

"Sean Timmons" wrote:

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

"kwalker4" wrote:

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!