Formatting & Formula Problem

[Guest]

Formula
=IF((R5<=$R$11),"5 days service",("delayed"))

The cell R5 is the result of a simple formula.
When the 'true' condition of the formula = True then it displays correctly.
When the 'false' condition of the formula = False then it displays ONLY the
true result.
If the result of R5 is typed in manually, that is not obtained from a
formula it will infact display as it should.

R S T

Service Days On Time Appt. Score in Service
ROW4 05 06:00 00 01:00 5 days service
ROW5 06 15:00 yes 5 days service
ROW6 05 09:00 00 04:00 5 days service
ROW7 05 00:00 00 01:00 5 days service



R11 -> 05 12:00

 

[Don Guillett]

I just tested your formula with the proper results. However,
=IF((R5<=$R$11),"5 days service",("delayed"))
you don't need all the ()
=IF(R5<=$R$11,"5 days service","delayed")

Don Guillett
SalesAid Software
(e-mail address removed)

 

[Guest]

Don, although your formula did work, and of course minimize any unnecessary
typing, my problem still exists. I believe it may be in the formatting of the
cell for some reason... When i manually type "06 15:00" the formula works.
Even though the result is the same when performed via Excel calculation the
result of the "IF formula" ALWAYS meets the true(5 day services) condition of
the formula, never the false(Delayed).

Thanks for any and all help you can provide.

 

[Guest]

TTT Please

Don, although your formula did work, and of course minimize any unnecessary
typing, my problem still exists. I believe it may be in the formatting of the
cell for some reason... When i manually type "06 15:00" the formula works.
Even though the result is the same when performed via Excel calculation the
result of the "IF formula" ALWAYS meets the true(5 day services) condition of
the formula, never the false(Delayed).

Thanks for any and all help you can provide.

 

[Pete_UK]

If you type 06 15:00 in a cell, Excel will take this to be a text
string, but the comparison in your IF statement will be looking for
numbers. Is your input meant to represent 6 days and 15 hours?

Pete

 

[Guest]

Yes, I understand that it is a string rather than a numeric... but that is
the problem. When I type the string, the formula works. Which makes no
logical sense to me...
When the numeric data that is obtained from another formula 'does it's
thing' and is then used in this formula... It doesn't work as it should.

Yes, 06 15:00 is 6 days and 15:00 Hours.

 

[Roger Govier]

Hi

What is the formula that is returning the value in R5 then?
You say it works if you type the value in R5, but not if Excel returns
the value.

 

[Pete_UK]

Presumably then, your value in R11 is also text, so you are trying to
compare the result of your formula (number) with a text value. If your
formula returns a number like 5.75 (5 days and 18 hours), then you
could wrap it in the TEXT function to convert it to text, like so:

=TEXT(your_formula,"dd hh:mm")

to get it in the format you want. Alternatively, you could have
everything as numbers and just apply a custom format to the cells to
display them as you wish - the format string is as given in the TEXT
function above. When you want to enter a number like "05 15:00", enter
it as "=5+15/24" (without the quotes).

Hope this helps.

Pete

 

[Guest]

The formula is:

=(M5-G5)-1

M5=5/24/07 15:00
G5=5/17/07 0:00

Thanks for all your help.
The next thing I could do is to create a link on my website to the
spreadsheet if that would help things...

 

[Guest]

Thanks everyone for all of your help.
I used your "=5+15/24" and it worked great.

 

[Pete_UK]

Thanks for feeding back - glad it worked for you.

Pete