Forum Discussion
Excel: accumulate time and display it in hours plus fraction of an hour
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".)
(Forgive me if this is a dupe. I'm not sure what happened to my last submittal.)
Re: ``i want to enter the beginning time as "3000.0" (flying hours) but cannot find the format which lets me do that``
I'm not sure I understand.
If you want to type literally 3000.0 and display 3000.0, simply format the cell as General or Number with 1 decimal place.
If you want to type literally 3000.0 in A1 and display it as 3000:0 (Excel time), you must convert it by entering the following formula into B1: =A1/24, formatted as Custom [h]:mm.
Alternative, you can enter literally 3000:0, formatted as Custom [h]:mm.
-----
Re: ``i would like to add a flight (of say 1:46) to the prior accumulated 3000.0 hours, and express this as "3001.8"
Again, I'm not sure I understand.
If you have literally 3000.0 in A1 and 1:46 (Excel time) in C1, you can enter the formula =A1 + C1*24, formatted as Number with 1 decimal place. That displays 3001.8. But the exact value is 3001.76666666667 (- 3.18E-12). If also want the exact value to be 3001.8, explicitly round: =ROUND(A1 + C1/24, 1).
If you have literally 3000:00 (Excel time) in B1, you can enter the formula =(B1+C1)*24, formatted as Number with 1 decimal place. Or again =ROUND((B1+C1*24, 1).
