Strange date problem with autofill

[James Martin]

Hi there,

I'm having a problem with autofill and dates/times where Excel loses a
second every 200 rows. To replicate it do this:

1. On a blank worksheet format column A as Custom "yyyy/mm/dd hh:mm:ss".

2. Put "2003/01/01 12:00:00" in A1.

3. Put "2003/01/01 13:00:00" in A2.

4. Select cells A1 and A2 and then fill down to A101.

You'll see that A100 has a value of "2003/01/05 15:00:00" but A101 has a
value of "2003/01/05 15:59:59" instead of "2003/01/05 16:00:00".

If, instead of filling down to A101, you fill down further, you'll find that
Excel loses another second at A301, A501, A701, etc.

I assume this is some goofy rounding problem, but does anyone know a way
around this (other than writing some VBA code to fill the cells instead of
using autofill)?

Thanks!

James

 

[Lance]

You can use in a2 and copy down

=a1+(1/24)

PS: Using autofil I lost a second at row 101 and then did
not lose any more time.