Summing hh:mm Time Values in Excel
Last updated on 2018-08-01 by David Wallis.
“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?”
Response — 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 [h]:mm:ss (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:
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:
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.
My Excel Formulae and Formats for Time Differences carries a full explanation of how Excel accommodates time and how we need to appreciate that when attempting to input times exceeding 24 hours.
Your Support for dmw TIPS
If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.
To make a contribution by PayPal in GBP (£ sterling) —
To make a contribution by PayPal in USD ($ US) —
To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —
Thanks, in anticipation.
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.