error message "#VALUE!" due to blank cells

[Dorian74]

Hi All,

I am trying to have a spreadsheet where i check the delivery time o
orders (attached a picture).
Essentially, it calculates the difference between the "Promised Dat
(A1)" and the "Atual Date (A2)" i.e. A1-A2
The problem is where is have blank cells with no information yet. then
get the nice error message "#VALUE!"

Can anyone help in having these cells show nothing instead of the erro
message?

Thank you

+-------------------------------------------------------------------
|Filename: PO Date.JPG
|Download: http://www.excelbanter.com/attachment.php?attachmentid=318
+-------------------------------------------------------------------

 

[Gord Dibben]

Are the cells really empty?

Maybe they have spaces in them?

When you hover over the error-checking arrows do you get a message?

What happens when you clear those cells?


Gord

 

[Vacuum Sealed]

Hi

Try this

=IF(A1="","",B1-A1)

change ranges to suit

HTH
Mick.

 

[joeu2004]

I am trying to have a spreadsheet where i check
the delivery time on orders (attached a picture).

For future reference, images that only duplicate what you describe in
English is usually useless.

What we need to know are: formulas, cell values, etc.

But in this case, the problem is "obvious" -- or so it would seem.

Essentially, it calculates the difference between
the "Promised Date (A1)" and the "Atual Date (A2)"
i.e. A1-A2
The problem is where is have blank cells with no
information yet. then i get the nice error message
"#VALUE!"
Can anyone help in having these cells show nothing
instead of the error message?

That cells that __appear__ blank probably are not "empty" (no constant and
no formula).

Instead, probably (hopefully) their value is the null string ("").
Alternatively, their value might strings of spaces or non-breaking spaces
(HTML &nbsp).

In either case, the following should avoid the #VALUE error:

=IF(COUNT(A1,A2)<>2,"",A1-A2)

formatted as General or Number to avoid a "negative date" error in the case
when "Actual" is later than "Promised".

Thus, the cell will __appear__ blank unless A1 and A2 have numeric values,
in this case Excel dates. In that case, the value is the null string ("").