excel - summing hh:mm time values

question

I have a column of cells in an Excel spreadsheet containing times like, 3:15, relating to the time spent on a task. At the bottom of the column I've used AutoSum to add up the times, but the result appears as either a date or funny number. What do I do to get a total in hours and minutes?

answer

The formula you created with AutoSum is fine.

All you need do is apply an appropriate format to its result:

• Select the cell containing the total
• From the Format menu, choose the Cells command
• From the Category list, select Custom
• In the Type box, input this format
  [h]:mm
  (note the use of square brackets).
  If you want to show seconds in your total, input
  [h]:mm:ss
  (this format is listed in Excel's Custom formats)
• Click OK.

disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.