Forum Discussion

em12345's avatar
em12345
Copper Contributor
May 22, 2019
Solved

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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 22, 2019

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)

  • em12345's avatar
    em12345
    Copper Contributor
    May 23, 2019

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 25, 2019

      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.

Resources