Excel 2013 time autofill problem I have a column containing date and time values

Question

Excel 2013 time autofill problem

I have a column containing date and time values with 5 mins differences between the rows, like this:

2014. 01. 01. 00:00:00

2014. 01. 01. 00:05:00

2014. 01. 01. 00:10:00

2014. 01. 01. 00:15:00

...

My problem is when I try to autofill the column, it works in the first 80-100 rows and then the autofill starts to miscount the time values by 1 sec first, then 2, 3 and go on, like:

2014. 01. 01. 08:10:00

2014. 01. 01. 08:14:59

2014. 01. 01. 08:19:59

...

2014. 01. 02. 12:39:46

2014. 01. 02. 12:44:45

or

2014. 01. 01. 08:10:01

2014. 01. 01. 08:15:01

2014. 01. 01. 08:20:02

When I change the first wrong time value manually, the autofill works properly in the next rows or some hundreds rows, but then the problem occurring again. I tried to change the number format, but it didn't help. The most strange thing is that sometimes it works perfectly without changing anything...

I must fill long columns (100 000 rows) like this almost every day and it's really tiring because of this problem?

Thanks for help !!

Explanation / Answer

Hi..

Hi,

These are small rounding off errors that are giving you problems. Try it like this. Put your first time in A1 then this formula in A2

=A$1 +(ROW(A1)*"00:05:00")

Now select A2 and tap F5 and in the 'Reference box type a2:a100000 | OK.

Now home tab | Editing group | Fill | Down and you should get a series of times of 5 minute increments. If you want to you can select these times and copy then right click. paste special, select values OK.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.