Date Calculation inconsistant results
 

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...

Date Calculation inconsistant results
 

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


" 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...

Date Calculation inconsistant results
 

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

wrote in message
...
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...