How do I find the average time?

[Guest]

I subtracted one time from another to get the HH:MM. Now I need to find the
average of all the times. When I Average(range of times) I do not get an
accurate time (I manually checked it by changing the HH:MM to straight
minutes. Is there a way to average time in Excel?

 

[Dave Peterson]

Time (and dates) are just numbers to excel.

=average(a:a)
should work.

Any chance that the times weren't enter correctly?

 

[Guest]

The times were entered as "12:36" etc., then as "9/13/06 12:36". I have
tried to change the formula to give me the minutes but that isn't working
either. We are a 24 hour facility and need to use the dates but the times
aren't calculating correctly.

 

[Dave Peterson]

So you want to ignore the dates and only use the times?

Maybe something like:
=SUMPRODUCT(MOD(a1:a10,1))/COUNT(a1:a10)

with a date/time in A1, this formula:
mod(a1,1)
will return only the time (format it nicely to check it)