Formula: Time Between (Multiple) - Question

[xkaliboor]

Hello!
What would be a formula to check multiple times and print a different result depending on the time?
They're very specific times:
If it's less than 10 minutes, print 1.5
If it's greater than 10 but less than 30 minutes, print 3.0
If it's greater than 30 minutes, print 5.0

Thanks so much in advance!

PS: I may not be able to be able to read your responses as often as I'd like to, but I'll still read and respond as quickly as I can.

 

[nils7]

Hi xkaliboor,

Try the following. I have assumed that your two time values are in cells A2 and B2.

The formula to use is:

=IF((B2-A2)>(30*0.04/60),"5.0",IF((B2-A2)>(10*0.04/60),"3.0",IF((B2-A2)<(10*0.04/60),"")))

Hope this helps,

Nils

 

[joeu2004]

What would be a formula to check multiple times and print a different result depending on the time?
They're very specific times:
If it's less than 10 minutes, print 1.5
If it's greater than 10 but less than 30 minutes, print 3.0
If it's greater than 30 minutes, print 5.0

It is unclear what form the "multiple times" are in.

If C1 is some number of minutes in decimal form (e.g. 9 for 9 minutes), then use:

=IF(C1 < 10, 1.5, IF(C1 < 30, 3, 5))

perhaps formatted as Number with 1 decimal place so 3 and 5 look like 3.0 and 5.0.

Note that we do not need to test C1>=10. It is implicit because C1<10 is false.

Also note that the opposite of "less than" is not simply "greater than".

I did my best to interpret what you meant. But you need to decide what results you want for C1=10 and C1=30, and change "<" to "<=" if necessary.

Finally, if C1 is Excel time, for example the resullt =B1-A1, where A1 and B1 contain Excel date and time (e.g. 14-Oct-2015 12:34), then use:

=iF(C1 < TIME(0,10,0), 1.5, IF(C1 < TIME(0,30,0), 3, 5))

 

[xkaliboor]

Thanks for the help, everyone! Both of these work differently but both work perfectly for what I need! (And for what I asked for.)
I just had to edit a couple of things but it worked.

Thanks again!

PS: this is the finished formula for the portuguese version of Excel
=SE((C3-C2)>(30*0,04/60);"5,0";SE((C3-C2)>(10*0,04/60);"3,0";SE((C3-C2)<(10*0,04/60);"1,5")))

Finalised 1.0 in english:
=IF((C3-C2)>(30*0.04/60),"5.0",IF((C3-C2)>(10*0.04/60),"3.0",IF((C3-C2)<(10*0.04/60),"1,5")))

 

[joeu2004]

PS: this is the finished formula for the portuguese version of Excel
[....]
Finalised 1.0 in english:
=IF((C3-C2)>(30*0.04/60),"5.0",IF((C3-C2)>(10*0.04/60),"3.0",IF((C3-C2)<(10*0.04/60),"1,5")))

A poor choice, IMHO.

First, 0.04 is a poor estimate for 1/24; it should not be used. You might as well write the correct arithmetic, which is 30/24/60 or 30/1440. But that is exactly what TIME(0,30,0) returns. And the latter is more flexible; a better paradigm when parameters change.

Second, we usually do not want to quote numeric values ("5.0" instead of 5.0). But that presumes you want numeric results; usually we do.

Finally, as I explained, there is no need to test all 3 conditions; one condition is implied by the mechanics of the IF() expression.

More to the point, your formula returns FALSE if C3-C2 is exactly 0:10:0. Probably not what you want at all. At least, nils's formula returned the null string; but that is still probably not what you want, IMHO.

Oh well, "you can lead a horse to water, but you can't make it drink".