Date Calculation Error in Target Cell

I am having trouble when I create a date calculation: =D2+35 where D2
is to be input as a date and the target is to be read as a date.
When D2 is empty the target cell adds in a date that is +35 of some
ghost date already in the D2 cell. You can see the date of 1/1/00 when
using the calculator and highlighting the D2 cell. The D2 cell is
actually empty.
If I enter the correct date into the D2 cell the calculation in the
target cell is correct. So the calculation works.

How do I remove the ghost date or get the target cell to read ### when
D2 is empty?

I have tried changing the 1900, 1904 format but it did not work. I
tried using calculations where the "+" sign was inside ( ) No good
either. I have other calculations that use "-" signs and they work
properly.

This false date will cause confusion when being used in the intended
application.

You can't "remove the ghost" : If you add an ampty cell to a number, Excel
will consider it as a zero you're not supposed to add a number to anything
else than a number).

So, 0 + 35 = 35, and Excel will give you the corresponding answer:
feb/4/1900 or feb/5/1904, depending on the preferences setting. Seems pretty
normal when you know the way Excel deals with date and time (have a look in
the Helpfile).

If you do want Excel to leave the cell blank when D2 is blank, change your
formula to: =IF(D2,D2 + 35,"")

The "ghost" will still be there, but it won't bother you any more


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - Macintosh

Spazzam wrote :

> I am having trouble when I create a date calculation: =D2+35 where D2
> is to be input as a date and the target is to be read as a date.
> When D2 is empty the target cell adds in a date that is +35 of some
> ghost date already in the D2 cell. You can see the date of 1/1/00 when
> using the calculator and highlighting the D2 cell. The D2 cell is
> actually empty.
> If I enter the correct date into the D2 cell the calculation in the
> target cell is correct. So the calculation works.
>
> How do I remove the ghost date or get the target cell to read ### when
> D2 is empty?
>
> I have tried changing the 1900, 1904 format but it did not work. I
> tried using calculations where the "+" sign was inside ( ) No good
> either. I have other calculations that use "-" signs and they work
> properly.
>
> This false date will cause confusion when being used in the intended
> application.