Averaging times that cross midnight?

[Smurfy]

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

[joeu2004]

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.

[Smurfy]

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.




--
Smurfy

[Spencer101]

Smurfy;1602282 Wrote:
> 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"




--
Spencer101

[Smurfy]

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




--
Smurfy