Forum Discussion
Autofill date time series keeps subtracting minutes?
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)
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.