Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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. -- Regards, OssieMac "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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). 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
thank you i will try
LTGP "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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
thank you i will try
LTGP "Mike H" wrote: 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" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging Time
"JLatham" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging time | Excel Discussion (Misc queries) | |||
Averaging Time | Excel Discussion (Misc queries) | |||
Averaging Time | Excel Discussion (Misc queries) | |||
Averaging Time | Excel Discussion (Misc queries) | |||
Averaging time | Excel Worksheet Functions |