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

Raeneuf If you want to round to integer hours, enter: =ROUND((D16-C16+G16-F16)*24, 0)&"hrs" 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.

Excel returns incorrect answer | Microsoft Community Hub

dscheikey
Bronze Contributor
Jan 09, 2023
Raeneuf
You can round your result without loss of accuracy.

=FIXED(SUM(((ROUND(D16-C16,15))*24)+((ROUND(G16-F16,15))*24)),2)&" hrs"

You won't get more than 15 decimal places displayed anyway. Nevertheless, your result is still correct.
- Raeneuf
  Copper Contributor
  Jan 09, 2023
  This formula does fix the issue, and please forgive my ignorance. But why do I need to use these functions when the answer to this function would be a whole number and does not require rounding? Is there something wrong with the previous formula?
  - JoeUser2004
    Bronze Contributor
    Jan 09, 2023
    Raeneuf wrote to dscheikey : ``why do I need to use these functions [....] Is there something wrong with the previous formula?``
    
    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:
    
    C16: 23:45
    D16: 25:45
    E16: (D16-C16)*24
    F16: ROUND(D16-C16,15)*24
    
    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.
JoeUser2004
Bronze Contributor
Jan 09, 2023
Raeneuf

If you want to round to integer hours, enter:

=ROUND((D16-C16+G16-F16)*24, 0)&"hrs"

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.

Forum Discussion

Excel returns incorrect answer

Resources