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 is 1.00 it rounds up to 1.25.

 

please advise!

 

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)

     

3 Replies

Sort By
  • 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.

  • 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