How do I get AutoFill to fill dates with time without rounding.

[PCF]

I've run into a problem when using autofill in Excel. I'm trying to autofill
dates in the following format dd/mm/yyyy h:mm:ss. For example, I have a
measurement time of

02/10/2008 12:28:00
and the next time is:

02/10/2008 12:28:15

I've highlighted both of these cells and then dragged the bottom corner to
autofill this pattern. This works for awhile until I get:

02/10/2008 12:40:29
instead of
02/10/2008 12:40:30

This doesn't make a big difference right away but after continuing this for
a couple of hours the time is way off.

Is Excel all of a sudden rounding this number? Is there a way of making it
so that exactly 15 seconds are added each time? Any help would be greatly
appreciated!

 

[Fred Smith]

To force Excel to use 15 second intervals, use a formula, as in:

=a1+time(0,0,15)

Then drag this down.

Regards,
Fred.

 

[T. Valko]

I've also run into this behavior when "atuofilling" times. Do it this way:

Enter your first date/time in a cell:

A1 = 02/10/2008 12:28:00

Enter this formula in A2 and copy down as needed:

=A1+TIME(0,0,15)

After you're done then you can convert the formulas to constants:

Select the range of formulas
Goto Edit>Copy
Then Edit>Paste Special>Values>OK

 

[MartinW]

Hi Biff,

Do know anything about why this happens and is it different
in separate versions?

In XL2000 replicating the OP's data I got down to
A3104 before it kicked in showing 03/10/2008 1:24:01
yet it seems the OP only got to A51

If it was just a matter of the binary to decimal problem you
would expect to get the same result no matter what version.

Even more puzzling is if I start with these two
like this without inputting a date
12:00:00
12:00:15
It goes all the way to A65536 without missing a beat.

Regards
Martin

 

[T. Valko]

If it was just a matter of the binary to decimal problem you would expect

to get the same result no matter what version.

You would expect that but it depends on how you "autofill".

Try this:

Enter 12:00 AM in A1. Enter 1:00 AM is A2 Select both A1 and A2 and drag
down to A7.

Now, select cell A7 *only* and drag down to A10.

Now, select cell A10 *only* and drag down to A14.

Now, select cell A14 *only* and drag down to A18

Now, select cell A18 *only* and drag down to A19

Now, select cell A19 *only* and drag down to A24.

Now, enter this formula in B1 and copy down to B24:

=A1=TIME(ROWS($1:1)-1,0,0)

Every result should be TRUE but they're not! (at least, in my version of
Excel 2002 I get several FALSE results)

Now, enter 12:00 Am in D1 and 1:00 AM in D2. Select both D1 and D2 and drag
down to D24 *without* stopping.

Enter this formula in E1 and copy down to E24:

=D1=TIME(ROWS($1:1)-1,0,0)

Every result is TRUE as you would expect!

I've gotten "burned" on this behavior in the past! I couldn't figure out why
23:00 <> 23:00.

It seems that the autofill "resets" or "adjusts" somehow once you stop and
then restart.

 

[MartinW]

Interesting, it's definitely one to keep an eye on. I'm surprised
I haven't run into it before. I can think of times when I should
have and that makes me a little uneasy that maybe my checking
and double checking wasn't up to scratch although I am usally
very thorough in that aspect.

Hopefully I avoided it by pure dumb luck.<g>
At least now I know to avoid it in the future.

Thanks
Martin