You are probably right about the 1 second error years.

I just did a calculation in the head.

Or maybe there are version differences, mine is XL03.

I am using Excel 2003. But I am quite sure that the version of Excel

makes no difference, at least when seconds are zero. [1]

By the way 10 minutes, 1/144, Excels nearest number is just a little too small.

I thought that might be the mistake you are making.

Yes, the time 0:10:00, which is the same as 10/1440, is represented

internally exactly as

0.00694444444444444,40589503386718206456862390041351318359375, whereas

the mathematically correct value would repeat 4 infinitely. So there

is a numerical "error" of less than -3.85494E-19.

However, you are forgetting that Shriil included a date; we are adding

0:10:00 to 9/23/2010 9:00 initially. As it happens, that date/time is

represented exactly, namely 40444.375. But since that date consumes

14 of the 53 bits (including the assumed bit to the left of the radix

point), there are fewer bits to represent 0:10:00.

In fact, 9/23/2010 0:10 is represented internally exactly as

40444.0069444444,452528841793537139892578125. So when we add 0:10:00

to dates near 9/23/2010, we are effectively adding

0.0069444444,452528841793537139892578125. Note that that is slightly

larger, not smaller, than the mathematically correct decimal fraction.

Of course, the vagaries of computer floating-point arithmetic are not

quite that simple. But the fact is: if we add 0:10:00 to 9/23/2010

9:00, the exact internal representation is

40444.3819444444,452528841793537139892578125 -- larger than the

mathematically correct decimal fraction.

However, what really matters is how that compares with the internal

representation of the constant 9/23/2010 9:10. In that case, the two

internal representations are exactly the same. So there is no

computational "error" in that case. It is as good as it gets within

the limits of 64-bit floating-point representation.

But over long additions the result becomes too large for a while!

The vagaries of floating-point arithmetic and floating-point

conversion are too difficult to make predictions. The fact remains

that when add 0:10:00 iteratively starting with 9/23/2010 9:00

(straight-forwardly, not following Shriil's requirements), the first

off-by-one-second time is less than the intended time, namely

01/10/2105 05:39:59. [2]

In fact, compared with the equivalent constant, the internal

representation of the result of adding 0:10:00 is:

* too high starting with 09/23/2010 09:50:00

* too low starting with 01/05/2088 11:10:00

* too high starting with 01/10/2105 05:39:59

* too low starting with 01/15/2122 00:49:59

* too high starting with 01/19/2139 19:19:58

* too low starting with 01/24/2156 14:19:58

* too high starting with 01/28/2173 08:59:57

* too low starting with 02/02/2190 03:59:57

* too high starting with 02/07/2207 22:29:56

* too low starting with 02/12/2224 17:39:56

....etc....

The formulas could be tweaked to avoid most of the errors,

In this case it seemed unnecessary though.

How do you know? I don't see anything in Shriil's OP that explains

his use of the column of times. If they are part of a lookup table,

his/her reliance on the "inaccurate" results (i.e. different from the

equivalent constants) might result in misbehavior of his spreadsheet.

Likewise if he/she uses the "inaccurate" results as lookup values.

It is simply my style to alert the clueless user to the problem and

the more-complicated solution, and let the user decide what is and is

not necessary for his purposes.

-----

Endnotes

[1] Some time ago, I discovered that in Excel 2003, TEXT(...,"h:m:ss")

does not always result in the same binary representation as the

equivalent constant. Ron Rosenfeld found that that was corrected in

Excel 2007. But I believe that is only for times with some non-zero

amounts of seconds.

[2] I had previously said the first off-by-one-second is 1/10/2105

2:00. First, that was the intended time; the actual time was

1:59:59. Second, I had used the wrong starting time, 9/24/2010 15:30

as I recall.