Look at the formulas Mike H and Dave Peterson offered, much cleaner than
mine, and no doubt faster in operation.

"JLatham" wrote:

You're correct in assuming that going 'across' midnight is causing a problem.
You don't see the problem because of the format of the cells containing the
total time. Those have been formatted to just show the hours and minutes of
a date:time entry. If you select the top cell showing 5:17 in it and use
Format Cells and set the format to General, I believe you'll see that it
actually contains 23.22014. In Excel's time keeping, the number to the left
of the decimal (23.) is the number of days after the base date of Jan 01,
1900 and the decimal portion (.22014) is the portion of a day, i.e. hours and
minutes expressed as a decimal. and .22014 of 24 hours is 5 hours 17
minutes. What you aren't seeing is the Jan 22, 1900 part!!+

But for those times that are on the same side of midnight, the integer
portion is 0, not twenty-something. That's why your average is coming out so
squirrely.

We need to get rid of the part of the result that is to the left of the
decimal. Assuming your example times are in Row 2 (not starting at 69),
then this formula for the total time at row 2 and filled down should fix
things for you:
=IF(C2<B2,24-B2+C2,C2-B2)-INT(IF(C2<B2,24-B2+C2,C2-B2))

That will give you the same values of 5:17, 5:23, 6:13, 2:10 and 0:19 for
the times involved and the AVERAGE() of those five times will be 3:52.

The caveat here is that release must be within 24 hours of sign in.



"Exec. Lt. GMP" wrote:

SIGN IN RELEASE TOTAL TIME
20:20 1:37 5:17
20:20 1:43 5:23
21:07 3:20 6:13
21:30 23:40 2:10
0:20 0:39 0:19

AVG SIGN IN TO RELEASE" 13:28

THIS AVERAGE SHOULD NOT BE 13 HOURS AND 28 MINUTES

ARGUEMENTS USED BELOW

=SUM(24-B5) +(C5) TO GET TOTAL TIME
=AVERAGE(D67:D71) TO GET AVERAGE

This is a copy spread sheet and formulas, I was given to determine how long
it takes for a Prisoner to bond out of jail.

I think the problem may be with entering 00:00 ( Midnight )to 00:59 (12:59
am) into a cell

what is wrong with this formula, I am attempting to average hours and
minutes.

GP