Date Calculation inconsistant results

  • Thread starter Thread starter debbiecundill
  • Start date Start date
D

debbiecundill

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...
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time Calculations Help 4
Minus Dates 5
Add Total to End of columns 2
Calculate Payment Days and Date 3
Detecting changes in results of formula 3
Copy Error 5
RefersTo command uses double quotes 2
VBA to run all variable combinations in a calculation model and store result 6

Back
Top