The bug is in your expectations, not in Excel's calculation.
Excel stores dates as the number of days since 1900 and time as
fractions of days. Thus
6/9/2005 9:15 AM = 38512 + 37/96
6/9/2005 5:45 PM = 38512 + 71/96
6/15/2005 9:00 AM = 38518 + 3/8
6/15/2005 5:30 PM = 38518 + 35/48
Of these inputs, only
6/15/2005 9:00 AM = 38518 + 3/8
has an exact finite precision representation (in either decimal or
binary). The other three must be approximated. When you use
approximate inputs, it should be no surprise that the results are only
approximately correct.
One minute is 1/1440 of a day.
=ROUND(result*1440,0)/1440
will round the approximate result to the nearest minute, whereupon the
two calculations will agree.
If the calculations were done in decimal, they would be (using Excel's
documented limit of 15 digits)
38512.7395833333
-38512.3854166667
----------------
0.3541666666
and
38518.7291666667
-38518.375
----------------
0.3541666667
which differ by 1 in the 10th decimal place. If you round Excel's
output to 10 decimal places, this is in fact the result that Excel gave you.
There are trailing digits in Excel's results because the calculations
were not done in decimal. Excel (and almost all other software) does
binary math. The actual precision limit is not 15 decimal digits, but
53 binary bits
http://www.cpearson.com/excel/rounding.htm
The decimal representations of the binary approximations are
38512.7395833333357586525380611419677734375
-38512.3854166666642413474619388580322265625
-------------------------------------------
0.354166666671517305076122283935546875
and
38518.7291666666642413474619388580322265625
-38518.375
-------------------------------------------
0.3541666666642413474619388580322265625
Which Excel correctly reported to its documented display limit of 15
decimal digits.
Without directly obtaining the exact binary approximations, you can
generally predict the magnitude of possible discrepancies by using
Excel's documented limit of 15 decimal digits, so that the first
calculation would be represented as
38512.7395833333???
-38512.3854166667???
-------------------
0.3541666667???
That floating point calculations will have finite precision
approximation issues has been a standard topic in computer calculation
for over half a century (long before Excel was a gleam in Bill's eye)
The characterization of this group as "Excel Application Errors" in
Microsoft's Office Discussion Groups Home is easily misinterpreted. The
standard name of the group is microsoft.public.excel.crashesgpfs, and
your post has nothing to do with Excel crashing. You would have gotten
an almost immediate response had you posted the question in Worksheet
Functions or General Questions, where it would have been more on topic.
Jerry
