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

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!

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.

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!

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 …

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.
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. 