IF and ISNA trouble

[piguy]

Not sure what I'm doing wrong. Tried a couple of things but cannot get to work. Trying to remove "######" from cell when time is left blank.

here is formula:

=IF(ISNA(F6-TIME(1,0,0)),"test",(F6-TIME(1,0,0)))

if there is no time entered in cell F6, I'd like it to say "test"

 

[adam087]

The ###### error actually means your column isn't wide enough, it's not an error with the value so you can't test for it.

Try
=IF(F6="", "Test", F6-TIME(1,0,0))

Regards
Adam

 

[Jonmo1]

Perhaps

=IF(F6="","test",F6-TIME(1,0,0))

 

[Jonmo1]

The ###### error actually means your column isn't wide enough, it's not an error with the value so you can't test for it.

Not so much that the column isn't wide enough..

It's that a blank cell Minus an hour is a Negative Time.
And Excel can't format a negative time, so it displays #########
It won't matter how wide you make the column.

But you're right that it's not technically an error..
It's just that it can't apply a time format to it because there's no such thing as negative time to Excel.

 

[piguy]

Thanks Jonmo. That'll do it!

 

[adam087]

Not so much that the column isn't wide enough..

It's that a blank cell Minus an hour is a Negative Time.
And Excel can't format a negative time, so it displays #########
It won't matter how wide you make the column.

But you're right that it's not technically an error..
It's just that it can't apply a time format to it because there's no such thing as negative time to Excel.

Nice clarfication, thanks. I'd never thought of it in that way before.

Regards
Adam

 

[Jonmo1]

Another interesting part to this is that the underlying value is still there.
Again, excel just can't apply a time format to it.

So even with a cell holding a negative time, and displaying ####
You can still do math on it.
Add say 2 hours to it, and you will have the correct result.

 

[adam087]

That is interesting because it struggles with some other numbers that exist but can't be displayed - SQRT(-1) is the obvious example - and it just errors out and won't let you work with it.

 

[Jonmo1]

That is interesting because it struggles with some other numbers that exist but can't be displayed - SQRT(-1) is the obvious example - and it just errors out and won't let you work with it.

So what is the square root of -1 ?


The difference here is that the function sqrt is returning an actual error.
The problem in the thread is that the subtraction function is working fine, Excel just can't format it as a time.

 

[adam087]

SQRT(-1) = i
i^2 = -1

http://en.wikipedia.org/wiki/Imaginary_number

:S

But I do take your point!

Regards
Adam