| Home |
| Search |
| Today's Posts |
|
#6
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 |
| 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 | |||
![Exec. Lt. GMP[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
