Custom Date format increments by 1 hour and 59 minutes

Prevost

Board Regular
Joined
Jan 23, 2014
Messages
198
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
You could use a formula so you don't accumulate error:

Row\Col
A​
B​
2​
01/01/2014 00:00​
3​
01/01/2014 01:00​
A3: =A$2 + "1:00" * (ROWS(A$2:A3) - 1)
4​
01/01/2014 02:00​
5​
01/01/2014 03:00​
998​
02/11/2014 12:00​
999​
02/11/2014 13:00​
1000​
02/11/2014 14:00​
 
Upvote 0
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
 
Upvote 0
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....
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top