Average Time

M

Mack

How do I calculate an average time?
I'm using 24 clock i.e. 13:45, and tried using the =AVERAGE
formula but this doesn't work, especially when the times
can vary between 13:45 and 00:45.

TIA

Mack
 
P

Peo Sjoblom

If you have start and end times,
to get the time after midnight

=MOD(end-start,1)

practical example

in A2 13:45 in B2 00:45

in C2 =MOD(B2-A2,1)

assuming that you have several start - end times, copy down the formula

then finally average the time

=AVERAGE(C2:C10)
 
M

Mack

Thanks, but they are not end-start times, let me explain
more clearly:

My company receives important data via ftp once a day,
usually around midnight (apart from one particular day, but
let's not confuse matters anymore!), I am trying to set up
a spreadsheet where we can record the time each day, and
then have an average time for the week, but like I say, the
trouble is sometimes it may be 23:15, then the next day
01:15. Unfortunately I have only basic Excel knowledge and
this is bugging me now - I though it would be quite easy!!

I've formatted the cells as time, not sure if I should
chnage it to number or even a custom one.

Thanks

Mack
 
A

AlfD

Hi Mack!

You could use minutes before (-ve) and minutes after (+ve) midnight
These you can average and then convert back to a clock time.

Al
 
Top