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.
It sounds like your *times* are really text. Try this on a Copy of you data:
Copy an unused cell then highlight the *times* and select Past Special > Add
I got:
20.41326
12.98186
6.893646
with your sample data.
Then reformat the cells as [H]:mm:ss (even if they were that format to start with)
It worked for me.
http://www.eggheadcafe.com/software/aspnet/29923507/sum-time–strange-result.aspx
You can make a =SUM(YourRange) and then change the format to custom – [h]:mm:ss
If the times are stored as text, you can use: =SUMPRODUCT(–A1:A20) formatted as jpaulino suggested, but just note that it won’t break the minutes out into hours. (I assume from your question, you have no hours indicated)
>>What means "–" ? It’s a convertion to numeric ?
>>
>>I think I’m learning something new today 🙂 I love this!
As Rory indicates, that’s exactly what it is. It’s sometimes referred to as "double unary". Having just the one minus sign can force a type conversion and multiply the converted numeric value by -1. The second minus sign multiplies again by -1, so the overall effect is to force a type conversion to a number and multiply be one (thus yielding itself).
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=670