Hi, I want to calculate the amount of time between two dates, using
the following:
=IF('Data Entry'!A2=0,,IF('Data Entry'!S2="",Background!A2-'Data
Entry'!D2,'Data Entry'!S2-'Data Entry'!D2))
My problem is, that on some rows the calculation works fine and will
return a meaningful result, yet in others it returns a result such as
"39696". The formula, formatting and all other inputs seem fine, and
there is no reason that I have been able to identify yet which is
consistent between the differeing result.

Any ideas?

Thanks so much...

Dates in XL are stored as long integers. Basically it is the number of days
since Jan 1, 1900. If you subtract today - 0 you end up with 39,644. Look for
places where you might be subtracting 0 or such...
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> Hi, I want to calculate the amount of time between two dates, using
> the following:
> =IF('Data Entry'!A2=0,,IF('Data Entry'!S2="",Background!A2-'Data
> Entry'!D2,'Data Entry'!S2-'Data Entry'!D2))
> My problem is, that on some rows the calculation works fine and will
> return a meaningful result, yet in others it returns a result such as
> "39696". The formula, formatting and all other inputs seem fine, and
> there is no reason that I have been able to identify yet which is
> consistent between the differeing result.
>
> Any ideas?
>
> Thanks so much...
>

Excel stores dates as serial numbers starting from 1/1/1900
Your number 39696 is Sept 5 2008
It is not the result that is inconsistent but the formatting of your cells
Just format your cell as a date using the local custom dd/mm/yy or mm/dd/yy,
etc
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

<(E-Mail Removed)> wrote in message
news:9826256a-8c76-4716-b380-(E-Mail Removed)...
> Hi, I want to calculate the amount of time between two dates, using
> the following:
> =IF('Data Entry'!A2=0,,IF('Data Entry'!S2="",Background!A2-'Data
> Entry'!D2,'Data Entry'!S2-'Data Entry'!D2))
> My problem is, that on some rows the calculation works fine and will
> return a meaningful result, yet in others it returns a result such as
> "39696". The formula, formatting and all other inputs seem fine, and
> there is no reason that I have been able to identify yet which is
> consistent between the differeing result.
>
> Any ideas?
>
> Thanks so much...