 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Complex IF formula w/ time
I need to create a spreadsheet for my boss scoring the length of calls. 4:32 means 4 min and 32 secs. The formula needs to have the following for scores: >5:00= 1 point 4:31-5:00= 2 points 4:00-4:30= 3 points 3:30-359= 4 points <3:30= 5 points I keep getting an errors when I attempt this I think its because of the time issue. I need to figure out how to format my time so it is min and sec. When I attempt to do that w/ the format option it changes my 4:32 to 32:00:00. If someone could walk me thru this I would be extremely appreciative. By what formatting process did you end up with 32:00:00 ? If you had typed in 4:32 would have expected you to get 32:00 if you formatted as mm:ss, and 272:00 if you formatted as [mm]:ss. This is because 4:32 is interpretted by Excel as h:mm, not m:ss. To get m:ss you need to enter the data as 4:32.0 or as 0:4:32. If you have vast amounts of data already entered as h:mm and you want to convert to m:ss, divide by 60. -- David Biddulph >I need to create a spreadsheet for my boss scoring the length of calls. >4:32 [quoted text clipped - 12 lines] > 32:00:00. If someone could walk me thru this I would be extremely > appreciative. The formula worked when I converted the time to 0:04:32, Thank you!!! I can't even begin to tell you how much time I have invested trying to make this formula work. > By what formatting process did you end up with 32:00:00 ? > If you had typed in 4:32 would have expected you to get 32:00 if you [quoted text clipped - 23 lines] > > 32:00:00. If someone could walk me thru this I would be extremely > > appreciative. Glad it helped. -- David Biddulph > The formula worked when I converted the time to 0:04:32, Thank you!!! I > can't [quoted text clipped - 29 lines] >> > 32:00:00. If someone could walk me thru this I would be extremely >> > appreciative. Erin, No need for a complex IF formula. With cell A2 formatted for time, and 00:04:32 entered in cell A2, use the formula =MIN(MAX(11-MINUTE(A2*2),1),5) and then copy down to match your list of times. HTH, Bernie MS Excel MVP >I need to create a spreadsheet for my boss scoring the length of calls. 4:32 > means 4 min and 32 secs. The formula needs to have the following for scores: [quoted text clipped - 9 lines] > 32:00:00. If someone could walk me thru this I would be extremely > appreciative. In case you were not aware, your ranges are not all the same length. Anyway, give this formula a try... =IF(A1>--"5:00",1,IF(A1>--"4:30",2,IF(A1>--"3:59",3,IF(A1>--"3:29",4,5)))) Rick >I need to create a spreadsheet for my boss scoring the length of calls. >4:32 [quoted text clipped - 12 lines] > 32:00:00. If someone could walk me thru this I would be extremely > appreciative. Wouldn't that be working in hours and minutes, rather than minutes and seconds? [That may, of course, be what the OP needs, if they've typed in 4:32, as that would be interpreted as 04:32:00, not as 00:04:32.] -- David Biddulph > In case you were not aware, your ranges are not all the same length. > Anyway, give this formula a try... [quoted text clipped - 19 lines] >> 32:00:00. If someone could walk me thru this I would be extremely >> appreciative. Yes, I just pasted a correction for that based on seeing one of the other responses (your message hadn't appeared in my newsreader yet); but in thinking about it, I'm not sure what the OP actually needs (so he'll have both to choose from then<g>). Rick > Wouldn't that be working in hours and minutes, rather than minutes and > seconds? [quoted text clipped - 27 lines] >>> 32:00:00. If someone could walk me thru this I would be extremely >>> appreciative. Now if I would have only typed the formula out correctly, that would have been nice. Use this formula, not the one I posted previously... =IF(A1>--"00:05:00",1,IF(A1>--"00:04:30",2,IF(A1>--"00:03:59",3,IF(A1>--"00:03:29",4,5)))) Rick > In case you were not aware, your ranges are not all the same length. > Anyway, give this formula a try... [quoted text clipped - 19 lines] >> 32:00:00. If someone could walk me thru this I would be extremely >> appreciative. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.