Forum Discussion
Autofill date time series keeps subtracting minutes?
Hi,
I am creating a date time series for every hour, for the years 2000-2019 in a column. I start by typing in the first cell: 1/1/2000 0:00, which has a custom format of m/d/yyyy h:mm. I want the cells to show the format month/day/year with 0:00-23:00 hours. I drag the cells to fill the series down the column, this results in cells for each of the 24 hours in each day (screenshot below).
The problem is that I am losing 1 minute at row 102, and after this I keep losing 1 minute after every 12000 rows. Basically, every 1.37 years I lose 1 minute. In the screenshot, after 1/5/2000 3:00 the following cell is 1/5/2000 3:59 (instead of showing time 4:00). Instead of displaying on the hour, it shows 1 minute less. This happens again on 5/19/2001 2:59, with the following cell time changed to 3:58. Losing another minute.
Could this be a bug? I don't know if it is leap year related, or why Excel is automatically changing the time series. I just want to keep the hourly format 0:00 to 23:00.
I'm using Excel MS Office Professional Plus 2019, Windows 10 operating system.
Thanks for any advice!
Date Time series changes by 1 minute starting at row 102, and then every 12000 rows
em12345 , that is floating point error https://support.microsoft.com/en-ca/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel since datetime value in Excel is actually just number, more about that is here https://www.myonlinetraininghub.com/excel-date-and-time.
As workaround you may type your initial date time in first cell, let say A1, in A2 add the formula
=MROUND(A1+1/24,"01:00")
and copy A2 down till end of your range (drag down or select the range starting from A2 and Ctrl+D)
4 Replies
If you wanted to settle for every 3hrs, your accumulation would be exact because a binary number can store 1/8 of a day with no error. Unfortunately the best it can do for 1 hour is about 1/20 of a second too small, an error that accumulates with every step.
What you can do instead, is index your datetimes with an integer and only then convert the index values to give date and time. Accumulating the integers will give a zero error (the result being the natural number line 'k', which could also be derived from the row number [or the SEQUENCE function for those who have it]).
The formula
= initialDate + k/24
will give the times you need with a repeating pattern of errors
exact -0.05 sec (approx) +0.05 sec (approx) exact -0.05 sec (approx) … All of which is better than simply accumulating rounding error until your calculation is swamped by it.
em12345 , that is floating point error https://support.microsoft.com/en-ca/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel since datetime value in Excel is actually just number, more about that is here https://www.myonlinetraininghub.com/excel-date-and-time.
As workaround you may type your initial date time in first cell, let say A1, in A2 add the formula
=MROUND(A1+1/24,"01:00")
and copy A2 down till end of your range (drag down or select the range starting from A2 and Ctrl+D)
Thanks for the info and reference to these articles SergeiBaklan !
I wish Excel would have a date time format that was text, or something to eliminate the floating point error issue. This makes me nervous about other excel calculations.
I wonder if the rounding function may cause issues with format or in Vlookup formulas? I found some other options to try, in addition to your MROUND suggestion (below). When I use either of these formulas they display the date and time correctly, but when I change the cell to a General format, the values are very different. Which function is best to use?
OPTION 1 Displayed Result Actual Number Value
A1 = 01/01/2000 0:00 01/01/2000 0:00 36526
A2 = A1 + TIME (1,0,0) 01/01/2000 1:00 36526.04167
A3 = A2 + TIME (1,0,0) 01/01/2000 2:00 36526.08333
A4 = A3 + TIME (1,0,0) 01/01/2000 3:00 36526.125
OPTION 2 Displayed Result Actual Number Value
A1 = 01/01/2000 0:00 01/01/2000 0:00 1
A2 = A1 + 1/24 01/01/2000 1:00 1.041666667
A3 = A2 + 1/24 01/01/2000 2:00 1.083333333
A4 = A3 + 1/24 01/01/2000 3:00 1.125
em12345 , they shall give exactly the same result, TIME(1,0,0) is only more friendly version of 1/24 when you are working with time. When you Fill series in background it also adds 1/24 to each next value. All variants are in the attached file.
I didn't catch how did you receive 1 in General format for the Jan 01, 2000. It shall be 36526, dates start from Jan 01, 1900 which is 1. Please check the row 102 in attached.
As for the VLOOKUP please may use rounded lookup value as well.
