Summing two columns in HH:MM:SS.000 in Excel

Question

It is most likely very trivial, but I simply cannot manage to sum two columns in the time format hh:mm:ss.000. ".000" indicate milliseconds. Summing 00:00:16.800 and 00:00:52.317 always result in zero instead of 00:01:09.117. I hope you can point me in the right direction and help troubleshooting this. Thanks!

I have tried different Sum formulas and format styles.

Answer 1

It's most likely because Excel thinks your cells are text and it doesn't implicitly handle that when you use SUM().

To work around this you can explicitly have Excel convert the cell value to a TIMEVALUE like so: =SUM(TIMEVALUE(A2:B2)).

When I test it with the explicit type conversion it works correctly:

Here is my test spreadsheet: https://1drv.ms/x/s!Atp1-xJxROCWgkvluQLmy64OVIEj?e=HlAMpc

If this doesn't work for you can you try re-creating the issue in a test spreadsheet? You can do so by cloning mine using the Edit a copy button in the top right: