Excel Time Drift

[icysummer]

Hope this is the right forum,
I'm using Excel 2007 in VirtualBox on a Macbook Early 2006. When I autofill a date/time in column A:

1: 1/1/2009 0:00:00
2: 1/1/2009 1:00:00
etc...

I find that after about 5 days, ie 5/1/2009, the time starts to drift, only by a second, but progressively gets worse over time.
looks like this

5/1/2009 4:00:01 or even 5/1/2009 3:59:59
10/1/2009 4:00:02
and so on...

My question is why does this happen (is it related to accuracy in excel) and is there some way to fix it?

This concerns me because I'm working with a large datasets that need to be merged with another dataset for statistical analysis and the merge doesn't work if the date/time match is not exact.

I've tested the autofill in openoffice and initial tests don't seem to have this problem. I know I could use openoffice, but at the moment a quick fix in excel would be preferable.

 

[oculus42]

Just an idea

type in the date on the first few fields, then change them to Numbers, expand the column, then change back to dates.

No guarantees, but it may be drift in how the dates are represented.

 

[mkrishnan]

I seem to be getting very strange behavior for the date/time field in Excel 2003 in Windows.

If I create a column that has a date/time field type, and I just enter a random number, say, "37000" in the first row, it gets converted into a date/time representation, in this case, 4/19/2001 @ 12:00AM. If I now do A2 = A1 + time(1,0,0) -- that function adds an hour to the time. If I fill the formula down, it was able to go on to the bottom of the spreadsheet (10/9/2008 @ 3:00PM) without any problem.

So it might be an option to use a formula with time() instead of autofill.

The weird part is that if I copy and paste the same formatted date/time text into the first A1 cell (instead of typing in 37000 or whatever), I get #value for the formula cells.

 

[benbondu]

I think it's just a quirky Excel behavior.

If you look at the difference between the cells when you autofill, you'll see the difference is 0.0416666666642413 between the 1st and 2nd cells (which is pretty close to 1/24 so that's fine). But then it immediately gets off. The next difference is 0.0416665509, then 0.0416666088 (which is far enough off 1/24th to make a second difference a few months down the road).

It seems to only have this trouble if you start exactly on the hour. If you start with 12:00:01, for example, it doesn't get tripped up. Instead, every difference is 0.0416666666642413 (a very small amount over 1/24) with every 3rd difference being 0.0416666666715173 (a very small amount over 1/24-- presumably as a self correcting measure-- very clever, Excel).

Starting exactly on the hour, Excel trips up early on, and then starts thinking "Hey, the first couple dates are a tiny fraction of a second less than an hour apart. I'll autofill based on this difference instead of a full hour." You can see that after the 3rd date, Excel gets into a nice groove of adding 0.0416666087985504 each cell with 0.0416666087912745 every third as a correcting measure. This is causing you to lose seconds every 3-4 months.