Forum Discussion

eienkisu's avatar
eienkisu
Copper Contributor
Feb 28, 2020
Solved

Excel time ceiling bug?

    Please refer to file attached.   I'm using ceiling function to round up time to nearest 15 mins which is 0.25 hour. If it's a whole number, it shouldn't round up. But strangely when the hour...
Formulas and Functions
  • PReagan's avatar
    PReagan
    Feb 28, 2020

    Hello eienkisu,

     

    Savia is correct. This is a floating point error. Read more here:

    https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/

     

    Instead of

    =CEILING(C2, 0.25)

    Use

    =CEILING(ROUND(C2, 4), 0.25)

     

Savia's avatar
Savia
Iron Contributor
Feb 28, 2020
Most likely there is a tiny rounding difference as Excel converts between times, binary, and decimal.

Resources