How to Get Average for Time in Excel 2007?

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

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."

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.

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.

One thing to watch for when setting time formats in excel is that the format "hh:mm" ignores the number of days and similarly "mm:ss" will ignore the number hours entered in the cell.

If you are getting strange looking results when manually calculating average times try putting square brackets around highest level unit of time in your format:

• [hh]:mm
• [mm]:ss

This will return the absolute number of hours or minutes - useful if your sum of times goes over 24 hours or 60 minutes

Simple Just click on Cell and Convert into 12 hr time

i.e. if you have 00:31:24 then convert it into Full date like this 12:31:24 AM.

After that you can use Average.

If you want duration time, not time of day, use the format "[h]mm:ss" and not the format "mm:ss". Normal math functions (e.g. average, sum, etc.) should then work.

None of this worked until I tried this formula

AVERAGE(IF(BC8:CG8=0,"",TIMEVALUE(TEXT(BC8:CG8,"H:MM:SS AM/PM"))))

I have dates with a time component (mm:dd:yyyy hh:mm:ss) in range BC8:CG8 They generally center around 9:00am on each day. I wanted to average the time of arrival to class. I wasn't interested in averaging the days into the value. Therefore, I used the TEXT command to extract only H:MM:SS AM/PM. Then I converted that to a TIMEVALUE. I then put an IF statement to screen out the students who never logged into the Zoom class. Then I averaged this with a matrix formula by hitting crtl/shift/enter. It finally worked. Probably could have just used an AVERAGEIF command, but it took me so many tries this was the first one that worked.