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
 

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,718
Office Version
2010
Platform
Windows
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​
 

Prevost

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

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

Forum statistics

Threads
1,082,323
Messages
5,364,589
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top