How to sum time in Excel

Question

I got a range of cells in an Excel file with values of time like "12:00" (hh: mm), which is stored as plain text, without specific cell formatting. I need to sum up all of the cells, but I got zero when I try the SUM function.
How can I get the total sum of a range of cells in the form "hhh:mm: ss"?

Answer 1

Assuming your data is located at [D12:D22]

Try this formulas:

• To convert a time in the form of text to a serial number use this formula:
  = TIMEVALUE(D12)

• To add time entered as a number use this formula:
  =SUM(E12:E22) and format the cell as: [hh]:mm:ss

• To add time entered as text use this formula array:
  = SUM( TIMEVALUE(D$12:D$22) ) and format the cell as: [hh]:mm:ss