Date Autofill --weird!

[alice]

When I was trying to use the autofill command to fill out
dates, for some reason, excel loses a minute.

Steps to reproduce:
1) In Column B, put down a number (let say "123") in Cells
B1 to B3000.
2) Format Column A as Date Format [mm.dd.yy hh:mm]
3) In Cell A1, put "01.01.04 00:00"
4) In Cell A2, put "01.01.04 01:00"
5) Highlight both cells and double click on the bottom
right corner, this should autofill column A with dates all
the way to A3000 Cell.

Actual Results:
From row 101, all dates dropped a minute! it became
[mm.dd.yy hh:59] all the way to row 3000.

I tried to use macro with the .autofill command, it still
produces the same thing.

Other weird stuff happened when i was trying to do this:
dim d as date
for i=1 to 3000
d=d +i/24 'just to add an hour
next i

after a couple iterations, it will start adding a second
to it!

Can anyone provide me with a solution as this is very
strange. I've tried on both excel XP and excel 97 and
produces the same result.

 

[Jim Rech]

Bear in mind that "time" in Excel and VB are in reality just fractional
numbers. For instance if you enter .5 in a cell and Time format it you get
Noon. Depending on the format you choose the displayed time will be rounded
to the nearest minute or second, etc.

If you put .12 in a cell and .120001 in another and time format them, they
will both appear as 2:52:48 because that's what they round to. But if you
did a fill down using these numbers they would eventually diverge as the
differences became greater. It's the real underlying number that's filled,
not the displayed number/time.

 

[alice]

Hi Jim,
Thanks for the explanation.
Can I get a solution to work around that please?
All I want to do is to autofill a column with dates
without losing a minute or adding a second to it. In
other words, what can i do to prevent the numbers from
diverging?

regards,
Alice

 

[Jim Rech]

Try this. Take your example with 1/1/04 0:00 in A1 and 1/1/04 1:00 in A2.
In B1 enter

=A2-A1

and change the number format of B1 to Number and 16 decimal places. Then
enter =1/24 in another cell, say D1. Format it to Number and 16 decimal
places too. You should see the problem. A true "hour" in Excel terms is in
D1 but the difference you're getting in B1 is slightly different. That
difference produces the problem after enough fill down cells.

A remedy may be to change A2 to a formula:

=A1+$D$1

and fill that down. That seem to be okay when I try it.