Averaging times that cross midnight?

I keep track of what times I complete various task at work. These times could be before or after midnight

I would like to do an average but i don't know how.

any ideas? or maybe I should just forget it on times.....

"Smurfy" > wrote:
> I keep track of what times I complete various task at work.
> These times could be before or after midnight
> I would like to do an average but i don't know how.
> any ideas? or maybe I should just forget it on times.....

You don't provide sufficient details for us to offer a concrete solution. I
can only offer some concepts.

The easiest thing to do is: keep track of the date and time in each cell.
For example, instead of recording just 11:00 PM and 1:00 AM, record
5/30/2012 11:00 PM and 5/31/2012 1:00 AM. If they are recorded in A1 and
B1, the time difference is easy to compute:

=B1-A1

formatted as Time or as Custom [h]:mm if the difference might be greater
than 24 hours.

Note: You can also format A1 and B1 with to display just Time, if you like.

Alternatively, if A1 and B1 contain just time, you might compute the
difference using:

=B1-A1+(B1<A1)

However, that works only if the difference between A1 and B1 is less than 24
hours.

As for the average, simply use the AVERAGE function with a range of cells
that contain the time differences.

Alternatively, if A1:A10 contains start times and B1:B10 contains end times,
use the following array-entered formula (press ctrl+shift+Enter instead of
just Enter):

=AVERAGE(B1:B10-A1:A10)

or

=AVERAGE(B1:B10-A1:A10+(B1:B10<A1:A10))

depending on whether dates are included.

Sorry it was so vague....

I'm not figuring any differences in times. just the average time a task was complete. But it may have been before or after midnight. Times are in hh:mm format using a 24 hour format.

this results in each of the min, avg, and max being off. The result I get is that the [***] min and max are backwards..

*** I think I was overthinking this ... I'll just switch the min, max formulas.

OK ... but now of course my avg is not right ...

an avg time between 23:30 & 00:04 results in 10:20

actually swapping the min/max won't work either ... it throws those off if the times end up all before or after midnight

uhg ...

Originally Posted by Smurfy

OK ... but now of course my avg is not right ...

an avg time between 23:30 & 00:04 results in 10:20

actually swapping the min/max won't work either ... it throws those off if the times end up all before or after midnight

uhg ...

Any chance you could post an example workbook with dummy data?

I'm not sure I'm understanding how you get to "23:30 & 00:04 results in 10:20"

Date Time


Minimum 00:09
Average 10:01
Maximum23:59


05/13/2012 23:59
05/14/2012
05/15/2012 00:40
05/16/2012
05/20/2012
05/22/2012
05/23/2012 00:09
05/27/2012 23:54
05/28/2012 01:27

10 characters to short? not anymore....