Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have these values


I highlighted all these cells and went to format cells -> custom -> and choose mm:ss

I then tried to use the built in average function in Excel 2007


The result is 0.0

Of course this is not the number result that it should be(I have not calculated it manually yet but I am sure it is not 0).

I am not sure if it has the fact to do when you click on the cell it has something like this

"12:08:00 AM"

I am not sure if that is what is screwing it up.

share|improve this question

6 Answers 6

up vote 3 down vote accepted

If you just want to do this quickly, select Time as the option from the drop down box and it should work as expected:

alt text

I have tried and cannot replicate your results, I think that you are messing up hours/minutes/seconds, Time fields are usually stored as hh:mm:ss, and then just displayed how you want. I recommend you try just using the built in Time field (as above) then try changing it later to hh:mm / mm:ss / hh:mm:ss, I think what is happening is you are storing as mm:ss, and displaying the average as hh:mm, or similar which is why you are getting weird results.

share|improve this answer
I think I know what happened. I had the fields as Time but I had average as decimal. When I changed it to time it worked. I then was able to use format cells and bring it down to mm:ss. – chobo2 Jan 30 '10 at 21:07
A very quick and efficient method, +1. – John T Jan 30 '10 at 23:12
Unfortunately this doesn't ignore zero duration fields which means that if you have e.g. a timesheet for a month for work, your average hour will be wrong until the end of the month. I did not get AVERAGEIF to work properly. Any ideas? – Julian Jan 15 at 11:41
@Julian - I disagree, it is actually doing it's job - an average of 3,6,0 across three fields makes for an average 3... If you don't want certain cells to appear in the average, don't include them, but I don't believe this is a problem. – William Hilsum Jan 15 at 16:55
I mean TIME DURATION, so the cell type must be duration and the values would be 00:35,00:00,08:46. doesn't work for me, might be a bug... – Julian Jan 15 at 23:34

To get the times formatted as "mm:ss" I had to enter them as follows:


i.e. zero hours, some minutes and some seconds.

Changing the format to Time displays the "00:" for the hours.

Then when I average them I get 03:17

share|improve this answer
Hmm, I got 3:16 as you can see below. Weird... – Connor W Jan 30 '10 at 20:46
@Connor W - I retyped my values so they were really in "mm:ss" format rather than "hh:mm" format as Excel assumed they were when I pasted them into the sheet. It could be just rounding error in the function when dealing with "hh:mm" rather than "mm:ss" – ChrisF Jan 30 '10 at 20:49
I get 3:16:53, this is rounded up. – William Hilsum Jan 30 '10 at 20:49
@Wil - yes - as I was working in "mm:ss". With "hh:mm" the time will be truncated. – ChrisF Jan 30 '10 at 20:51

I just inputted the values you gave to Excel, set it to give an average and it gave back 3:16. You can see from the pics the formula for the average and the value of each cell. I set all the cells to "Time".

alt textalt text

Hope that helps

share|improve this answer
What did you choose for Time though. There are lots of options? – chobo2 Jan 30 '10 at 20:49
Your images are broken, are you able to reupload them? – 8088 Sep 13 '11 at 2:53

I was trying to do some math on time values, and I ended up here at this page looking for help. Strangely, though, none of the cell format suggestions listed here were working for me...

...until I finally realized that my data, which I'd imported from a file, had spaces in front of most of the time values. Once I removed the spaces, Excel was HAPPY to run all manner of formulas, correctly, on my time data.

Something to look for if you're having trouble for what seems like "no reason."

share|improve this answer

First, highlight all of the cells that you will be using for the calculation. (The cells containing the values that you will be adding, and the cell containing the average of all of the times.) Right click the collective highlighted cells, and click "Format Cells". In the Number Tab, choose Custom from the selections on the left side of the window. In the type drop down selection box, look for the value "h:mm". Select that option, and click ok.

The formula for the cell containing the average will be the same as any other type of average ( =SUM(XX:XX)/X ,OR =AVERAGE(XX:XX) )

Using this format, I'm given values such as this

3:55 3:58 4:14 3:22 3:49 4:07 4:02

AVG = 3:55

I hope this helps you out.

share|improve this answer

The approached I used with MS Excel 2010 (which will be similar to MS Excel 2007) was:

  1. Convert your time data into a decimal number. To do that just use the Format Cells dialog box; in the Number tab, click on the Number category. This will change all time data into a number.
  2. Calculate the average from that data and lastly re-convert that number into a time figure using the same approach described in step 1.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.