Custom Date format increments by 1 hour and 59 minutes

[Prevost]

Hi There,

I have a custom date format cell in the form of DD/MM/YYYY hh:mm . When I fill the series, it increments the hour value by one each time, which is what I want to happen. However, after a bunch of cells, instead of incrementing by 1 hour, it increments by 59 minutes. So it goes 01:00, 02:00, 03:00, 03:59, 04:59....and so on. It still works because the time being off by one minute doesn't matter in this scenario, but does anyone know why this happens?

Thanks!
Scott

 

[gsistek]

Are you using a formula to increment the hour? If so, what is your formula. It may be a rounding issue.

BTW - Love your avatar! Great episode.

 

[shg]

You could use a formula so you don't accumulate error:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

2​

[/td][td]

01/01/2014 00:00​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

01/01/2014 01:00​

[/td][td]A3: =A$2 + "1:00" * (ROWS(A$2:A3) - 1)[/td][/tr]

[tr][td]

4​

[/td][td]

01/01/2014 02:00​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

01/01/2014 03:00​

[/td][td][/td][/tr]

[tr][td]

998​

[/td][td]

02/11/2014 12:00​

[/td][td][/td][/tr]

[tr][td]

999​

[/td][td]

02/11/2014 13:00​

[/td][td][/td][/tr]

[tr][td]

1000​

[/td][td]

02/11/2014 14:00​

[/td][td][/td][/tr]
[/table]

 

[Prevost]

Thanks for the responses. I was not using a formula, I literally just typed the date and time into the cells for several cells so that Excel would pick up the pattern. I will try out shg's formula. Special thanks to gsistek for the avatar shout out. It's what I live for! Check me out on other Excel forums and stack exchange, I have the same pic... :D

 

[Prevost]

So I thought I had solved this problem, however it is not. I am not using a formula, but I don't see why I should need to? so I fill out a date in the format [DD/MM/YYYY hh:mm:ss] and yet for some reason, it continues to increment, but now it increments the seconds. Starting at 01/07/2012 01:00:00 and incrementing by one hour, so next cell is 01/07/2012 02:00:00, on the 103rd cell, it increments the seconds by 1! What is going on?

But BTW, I did enter in the time in the first cell, and then in the second cell I used the formula

=R[-1]C+TIME(1,0,0)

and that worked....