Jan 09 2023 09:53 AM
Jan 09 2023 09:53 AM
I am having an issue with excel returning the incorrect answer when formulating time worked into hrs. worked. An example of the formula used is =SUM(((D16-C16)*24)+((G16-F16)*24))&"hrs". In this instance the times entered are 23:45 to 2:45(entered as 26:45) and 22:15 to 1:15(entered as 25:15). The answer returned is 5.999999999hrs instead of 6hrs.
Any help that can be given is greatly appreciated.
Jan 09 2023 11:04 AM - edited Jan 09 2023 11:10 AMSolution
If you want to round to integer hours, enter:
Note the many simplifications:
1. SUM is unnecessary in this context.
2. Multiply by 24 (convert to hours) only once, after all time arithmetic instead of piecewise.
3. Remove unnecessary parentheses to improve readability. No need for subexpressions in this context.
4. No need to round subexpressions to 15 decimal places. It is also wrong, in general -- although it makes no difference in this context.
Jan 09 2023 11:11 AM
Jan 09 2023 01:34 PM - edited Jan 09 2023 01:40 PM
Not sure what "previous formula" refers to: yours or mine. But to answer your question: no, there is no need for all those functions.
There is a lot of misinformation and misunderstanding on the internet regarding non-integer arithmetic in Excel (and most applications), including MSFT documentation. Confusion between "15 decimal places" versus "15 significant digits". Confusion about the precision of non-integer arithmetic, in general. And confusion about the precision that Excel "stores" versus "formats", in particular.
The details might be TMI. But LMK if you are interested.
Rounding time and time arithmetic to 15 decimal places is wrong, in general. It can result in the wrong results.
If you are rounding to integer hours, or even decimal hours to 2 decimal places, you probably will not see a difference. But to demonstrate, consider the following example:
Format E16 and F16 as Number with 14 decimal places (15 significant digits, the most that Excel formats). E16 displays 2.00000000000000 as expected, whereas F16 displays 1.99999999999999.
(But don't be misled: E16 is not exactly 2, even though E16=2 returns TRUE -- an Excel trick that is misleading. To make that point, note that E16-2=0 returns FALSE. The two expressions are equivalent mathematically.)
The point is: D16-C16 formatted as Number with 16 decimal places is 0.0833333333333333. So we lose precision when rounding to 15 decimal places.
In general, if we choose to round, we should round to the precision that we expect or require. But when to round is a judgment call. Often, it depends on the context.
In the case of time arithmetic (resulting in Excel time, not decimal time), we do not know what precision to expect. On the contrary, usually we want to retain the full precision that Excel allows -- which is more than 15 significant digits, not to mention 15 decimal places.
I hope that helps. If not, I can certainly provide more details.
Jan 09 2023 01:40 PM