Thank you for the explanation. It is helpful when dealing with dates in
calculations.

"David Biddulph" wrote:

Yes, that's what either of my 2 formulae will do for you. 1 January 1900 is
the Excel date for a value of zero. If you don't want it to use that value
of zero, you need to tell Excel not to do the arithmetic with that cell in
that situation. If you tell Excel to do the arithmetic, and if the value in
the cell is zero, then it is treated as 1 January 1900.
--
David Biddulph


Basenji wrote:
Thank you for the formulas. They provide a solution for the negative
number for the difference of the two dates when the one date is
missing. However, is there a way to prevent Excel from "assuming" a
date of January 1, 1900, when no date is entered?


"David Biddulph" wrote:

= IF(COUNT(D2,J2)=2,J2 - D2,"")
or
= IF(OR(D2="",J2=""),"",J2 - D2)
--
David Biddulph

Basenji wrote:
Using Excel 2007 data has been imported from Crystal Reports into an
Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm
Date. Column L is used to calculate the difference between the two
dates, L2 = J2 - D2. Column L is formatted with a general number to
show the number of days between the two dates. While the Proc Date
is available the Confirm Date is not always available, so the cell
is blank. In the calculation, L2, the numer that is returned is
-40190, indicating that it is taking January 1, 1900, and
subtracting January 12, 2010. Is there a setting or some other way
to prevent a default date from being inserted when there is none so
that ##### shows in the calculated cell?


.


.