weird Calc results from elapsed-time formula, specific times only

[dpmaddalena]

In Calc, I use a formula for getting the elapsed time between entries, and the output of the formula returns a 2 or 3 digit number representing minutes elapsed between clock times. The formula:

=(B1-A1)*24*60

where I manually enter the times (A1 and B1) as HH:MM, formatted as 24-hour for display. For most values, this returns results like this

15:00	15:53	53

However, when the two clock times are roughly between 7:55 and 8:55 AM (the exact limits shift, and I can’t keep track of why), and usually ONLY if they are 54 minutes apart OR LESS, they return a number with an inexplicable fraction (13 decimal places!) as here:

08:00	08:53	53.0000000000001

or here:

08:02	08:56	54.0000000000001

or here:

07:57	08:49	52.0000000000001

or here:

07:55	07:56	0.999999999999996

BUT, not here:

08:01	08:56	55

nor here,

08:43	08:57	14

nor here

07:55	08:48	53

I could probably narrow down the specific limiting factors, but I am not that patient. If you ask me why I chose the formula I use for this, my answer is, ‘because I did a search and this was the simplest one that worked for me’, not because I understand exactly how it works–I’m not that strong a numbers guy. But I think I can see why it should work, and why it should not return fractional results!

Very confused and hoping for help. The weird results are practically NBD, as I get the info I need from the answer, even if it has 13 decimal places. But, while I’m not good at math, I would like for my math to work and make sense, so I’m pretty bothered by this.

thanks for any help!

[ve3oat]

I don’t know the reason for your problem, although it might be caused by a peculiarity in the floating point arithmetic of converting between hours and minutes and back again in LibreOffice, as evidenced by the difference in the 13th decimal place. Floating point arithmetic sometimes produces these quirky little differences when we think the answer should be exact.

A simple solution might be to just format the cell containing the calculated time difference to display zero decimal places. Menu Format → Cells and under the Numbers tab make sure the cell is a Number (rather than anything else) with the Option of zero for decimal places. Or, you could try applying the ROUND function to your time difference and specify zero decimal places so that it rounded off as a whole number.

[dpmaddalena]

Thanks for this @ve3oat. Setting the number’s decimal points to zero is clean enough for me in terms of display. I appreciate the comment. If I was feeding the result into any other formula, I’d try using ROUND to clean it up. Thanks again!

[newbie-02]

there is one more point to consider: time values represented as a fraction of a day being represented by ‘1’ have ‘odd fraction’ for most hour, minute and second values:
one hour being 1/24 being decimal ~ 0,041666666666667,
one minute being 1/24/60 being decimal ~ 0,000694444444444,
and one second being 1/24/60/60, decimal ~ 1,15740740740741E-05,
which are all ‘endless’ and therefore rounded fractions, this introduces imprecision only cancelled out for hours multiples of 3, minutes multiples of 9 and seconds multiples of 27 …
thus expect small imprecision for most values, and expect them to boost up and become visible in some situations, especially when subtracting similar values …

[SM_Riga]

Hello @dpmaddalena

As @ve3oat correctly stated, the reason is Floating-point arithmetic and it’s accuracy problems dealing with the real numbers and their non-representability in binary systems in this case. Please also refer to @Lupp example given in this thread and @mikekaganski answer here.

In a few words, some numbers can not be represented in the computer arithmetic exactly with the same value as they have in the real world mathematics and these numbers are defined with the finite precision, so performing operations on such a numbers leads to ‘weird’ results. As the time is represented in calc as a fraction of the day (more information about it you can find here), some values evaluates to such a non-representable numbers and this gives you these ‘weird’ decimals. What comes to workarounds - @ve3oat already gave them.

[dpmaddalena]

thank you very much, @SM_Riga. You’ve confirmed for me that the math behind my problem will remain incomprehensible for me! Nice to know that the problem is not my fault; thanks for the references and for the encouragement to pick a workaround and be satisfied.

[ve3oat]

In most practical situations it is safe (and satisfying) to ignore these little inconsistencies. After all, your 0.0000000000001 minute difference amounts to only 0.00006 microseconds. It is probably an understatement to say that is small.

[dpmaddalena]

But it’s billable! lol.