Forum Discussion
Excel: accumulate time and display it in hours plus fraction of an hour
I would like to accumulate time and display it in hours plus fraction of an hour, e.g.
11:30 + 9:15 + 6:45 = 26.5 hours
5 Replies
jim_wixson: If A1 is 11:30, A2 is 9:15 and A3 is 6:45, ostensibly enter:
=SUM(A1:A3)*24
formatted as General or Number. BTW, the result is 27.5, not 26.5 (sic).
It would be better to explicitly round to the precision that you want to be accurate. For example:
=ROUND(SUM(A1:A3)*24, 2)
Otherwise, the result might not be treated as exactly 27.5 in some contexts, due to inconsistencies in Excel and anomalies of 64-bit binary floating-point arithmetic.
For example, with the unrounded SUM formula in A4:
=A4=27.5 returns TRUE. But =A4-27.5=0 returns FALSE (!). Also, =MATCH(27.5,A4,0) returns #N/A, indicating a non-match (!).
Actually, the latter two are correct results, IMHO. The first result is due to games that Excel plays to hide binary arithmetic anomalies. But the "games" are implemented and applied inconsistently, as demonstrated.
an excellent and thoughtful reply; thanks.
I now have one other request: i want to enter the beginning time as "3000.0" (flying hours) but cannot find the format which lets me do that.
Can you tell me?
(Eg. i would like to add a flight (of say 1:46) to the prior accumulated 3000.0 hours, and express this as "3001.8".)
- A1 - 11:30
A2 - 9:15
A3 - 6:45
In cell A4, enter the formula: =A1+A2+A3
