Hi All,

I am trying to have a spreadsheet where i check the delivery time on orders (attached a picture).
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?

Thank you!

I think you may have got the cells in your formula above wrong. A1 looks like a header cell rather than one that contains a date.

It's simple enough to make the error message not show using:

=IF(promised_date="","",promised_date-actual_date)

and just swap promised_date and actual_date with the correct cell refs and copy down.

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

On Wed, 4 Apr 2012 15:17:35 +0000, Dorian74
wrote:


Hi All,

I am trying to have a spreadsheet where i check the delivery time on
orders (attached a picture).
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?

Thank you!


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

Hi

Try this

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

change ranges to suit

HTH
Mick.

"Dorian74" wrote:
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 a formulas, cell values, etc.

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


"Dorian74" wrote:
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 ("").

Thank you for the answer. that did the trick.