"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?
> >>
> >>
> >> .
>
>
> .
>
|
Similar Threads
