Complex IF formula w/ time

[Erin Leva]

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.

 

[David Biddulph]

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.

 

[Bernie Deitrick]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[David Biddulph]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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?

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

=IF(A1>--"5:00",1,IF(A1>--"4:30",2,IF(A1>--"3:59",3,IF(A1>--"3:29",4,5))))

Rick

 

[Erin Leva]

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
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
means 4 min and 32 secs. The formula needs to have the following for
scores:
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.

 

[David Biddulph]

Glad it helped.
--
David Biddulph

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