Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation results as static data not formula. | Excel Worksheet Functions | |||
Help please... Simple calculation - with unexpected results | Excel Worksheet Functions | |||
Calculation on SUMIF Results | Excel Worksheet Functions | |||
Conditional formatting producing inconsistant results? | Excel Discussion (Misc queries) | |||
Save calculation results | Excel Programming |