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)

     

PReagan's avatar
PReagan
Bronze Contributor
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)

 

  • Galtim's avatar
    Galtim
    Copper Contributor
    Dec 31, 2021

    PReagan Thank you so much for showing this. This solved my problem. The rounding issue draw me crazy.

Resources