How to get Average for time in Excel 2007?

I have these values

01:15
05:00
01:31
02:00
02:21
02:39
03:29
08:00

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

=AVERAGE(D31:D38)

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.

Solution 1:

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

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.

Solution 2:

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

00:01:15
00:05:00
00:01:31
00:02:00
00:02:21
00:02:39
00:03:29
00:08:00

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