Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formula returns night time by mistake | Excel Discussion (Misc queries) | |||
Calculating Time with Date/Time checking in formula | Excel Worksheet Functions | |||
convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |