“Thanks for the Excel Response for Summing Time Values — Worked Perfectly”

AT, Wisconsin (2010)

Summing hh:mm Time Values in Excel


We 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 — With Proviso

The formula you created with Σ AutoSum is fine.

For old versions of Excel, pre-ribbon, 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 the format [h]:mm
    (note the use of square brackets).
    If you want to show seconds in your total, input
    (this format is listed in Excel’s Custom formats)
  • Click OK.

For versions of Excel with the ribbon, all you need do is apply an appropriate format to the total formula:

Excel summing time

Find the format on the ribbon's Home tab, under Number, by choosing More number formats from the drop-down list to display the Format Cells dialog box:

Excel time format

The Proviso

The solution described above works provided each and every “Time Spent” is less than 24 hours. If any of them are likely to be longer than 24 hours, then you will need to pay particular attention on how you input them.

DMW's Excel Formulae and Formats for Time Differences Rumination carries a full explanation of how Excel accommodates time and how we need to appreciate that when attempting to input times exceeding 24 hours.


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.