Search code examples
excelexcel-formulaexcel-2007

Excel between times returns different values in same range

I am using excel to calculate total minutes between times.

My calculation formula is like this: =INT((M3-L3)*1440)

Time1                   Time2                Minutes
5.1.2016  21:00:00      5.1.2016  22:00:00   59
5.1.2016  22:00:00      5.1.2016  23:00:00   60

First result is 59 but second result is 60.

What is the problem here?

Solution

  • Excel internally represents date/time values as double precision floating point numbers. The integer part represents the day. The decimal part represents the time. Floating point numbers have a finite precision (about 15 significant base-10 digits). This means that some numbers can not be represented exactly which introduces small approximation errors that can lead to unexpected results.

    In your example, the first calculation is carried out as...

    =INT((5.1.2016 21:00 - 5.1.2016 22:00)*1440)
=INT((42491.875-42491.9166666667)*1440)
=INT(0.041666666664241*1440)
=INT(59.9999999965975)
=59

    The second calculation is carried out as...

    =INT((5.1.2016 22:00 - 5.1.2016 23:00)*1440)
=INT((42491.9166666667-42491.9583333333)*1440)
=INT(0.041666666664241*1440)
=INT(60.0000000069849)
=60

    This is a limitation of floating point math.

    In this specific case, you could get the desired results by replacing INT(number) with ROUND(number,0) or MROUND(number,1)