Averaging Time

[Exec. Lt. GMP]

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(D6771) 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

 

[Mike H]

Hi,

I have assumed you time in is in column A and out in column B. Use this in
column C to get the 'Held time'

=(B2-A2+(B2<A2))

Note that this works for time over midnight

Now average column C in the normal way

=AVERAGE(C2:C6)

Note if you multiply this last fromula by 24 and format as general you get
the answer in decimal.

Mike

 

[JLatham]

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.

 

[OssieMac]

Times are actually a fraction of a day. When entering times if you enter the
date with the time then you are more likely to get the results you expect.

Midnight can be entered 2 ways. If you enter Jan 1 2010 24:00 then it will
actually convert to Jan 2 00:00. The reason for this is that 24:00 hours is a
full day (or 1) and therefore it rolls over to the next day. However, if you
want to enter Jan 1 00:00 then it will remain as Jan 1 00:00 so you need to
think about what time you actually want.

When formatting times, use the square brackets around the hours to prevent
summed times converting to days and time and therfore displaying incorrectly.

Example format as [hh]:mm

Example if you sum 12:00 and 15:00 it should be 27:00 but if not formatted
with the square brackets it will display as 03:00. Looking at the formula bar
it will look like 1/01/1900 3:00:00 AM because it has added one day to the
time and shows the remaining 3 hrs.

Note that you can enter the times with the dates and still format as hours
and minutes so that the dates are not actually displayed on the screen but
they are still there for the purposes of calculation.

Try the above and see if it fixes your problem.

 

[Dave Peterson]

I would include both the date and time in both fields. Then I wouldn't have to
worry about prisoners who took longer than 24 hours to get bailed out (or longer
than 48 or 72 or...).

And then format the difference as:
[hh]:mm

As well as the average.

ps.
In this formula:
=SUM(24-B5) +(C5)
You don't need =sum()

You could have used:
=24-b5+c5

Mike H's formula:
=B2-A2+(B2<A2)

is short hand for:
=b2-a2 + If(b2<a2,1,0)

And 1 is equal to a single day (24 hours).

 

[JLatham]

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

 

[Exec. Lt. GMP]

thank you i will try

LTGP

 

[Exec. Lt. GMP]

thank you i will try

LTGP

Hi,

I have assumed you time in is in column A and out in column B. Use this in
column C to get the 'Held time'

=(B2-A2+(B2<A2))

Note that this works for time over midnight

Now average column C in the normal way

=AVERAGE(C2:C6)

Note if you multiply this last fromula by 24 and format as general you get
the answer in decimal.

Mike

 

[Exec. Lt. GMP]

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