Averaging Duration of Time: Duration Longer than 24hr
 Index ‹ Excel ‹ Excel
Author Message
Moroni

 Posted: Thu Apr 10 18:05:41 CDT 2008 Top
 Excel >> Averaging Duration of Time: Duration Longer than 24hr Im trying to average duration of time, and have not found the correct way to format the cell. First, I have a start and end time. I need to calculate the difference between the two in hours, and the duration usually exceeds 24 hours. Then I need to average the time durations. Sample: A1 (start time): 3/27/2008 8:35 Custom format: m/d/yyyy h:mm B1: (end time) 3/30/08 13:00 Custom format: m/d/yyyy h:mm C1: (duration) formula B1-A1 - value = 76:25 Custom format: [h]:mm (this seemed to be the only format that returned the duration in hours appropriately) I average the values in column C, and end up with a number greater than my longest duration. Custom format of [h]:mm or m/d/yyyy h:mm do not provide correct results. Suggestions are greatly appreciated. THANX! Excel260

Dave

 Posted: Thu Apr 10 18:05:41 CDT 2008 Top
 Excel >> Averaging Duration of Time: Duration Longer than 24hr Maybe it's time to share some of the values (10 rows or so???). Make sure that they have the same problem that you've experienced. > > Im trying to average duration of time, and have not found the correct way to > format the cell. > > First, I have a start and end time. I need to calculate the difference > between the two in hours, and the duration usually exceeds 24 hours. Then I > need to average the time durations. > > Sample: > A1 (start time): 3/27/2008 8:35 Custom format: m/d/yyyy h:mm > B1: (end time) 3/30/08 13:00 Custom format: m/d/yyyy h:mm > C1: (duration) formula B1-A1 - value = 76:25 Custom format: [h]:mm (this > seemed to be the only format that returned the duration in hours > appropriately) > > I average the values in column C, and end up with a number greater than my > longest duration. Custom format of [h]:mm or m/d/yyyy h:mm do not provide > correct results. Suggestions are greatly appreciated. > THANX! -- Dave Peterson

Robin

 Posted: Thu Apr 10 18:55:01 CDT 2008 Top
 Excel >> Averaging Duration of Time: Duration Longer than 24hr I ran the average formula for the 10 rows of data below and it seemed to return the correct duration. So I returned to column C and found one cell that was not formatted correctly. I think the average on column C works now. THANX A B C Start End Duration m/d/yyyy h:mm m/d/yyyy h:mm [h]:mm 3/18/2008 22:20 3/27/2008 17:00 210:40 3/26/2008 12:30 3/28/2008 17:00 52:30 3/27/2008 18:30 3/28/2008 0:01 5:31 3/23/2008 2:30 3/27/2008 14:00 107:30 3/29/2008 4:30 3/29/2008 22:00 17:30 3/31/2008 4:25 4/1/2008 2:00 21:35 3/28/2008 6:30 3/30/2008 14:00 55:30 3/27/2008 8:35 3/30/2008 13:00 76:25 3/26/2008 21:00 3/28/2008 17:00 44:00 3/27/2008 2:00 3/28/2008 2:00 24:00 3/26/2008 10:30 4/1/2008 8:30 142:00 > Maybe it's time to share some of the values (10 rows or so???). Make sure that > they have the same problem that you've experienced. > > > > > > > Im trying to average duration of time, and have not found the correct way to > > format the cell. > > > > First, I have a start and end time. I need to calculate the difference > > between the two in hours, and the duration usually exceeds 24 hours. Then I > > need to average the time durations. > > > > Sample: > > A1 (start time): 3/27/2008 8:35 Custom format: m/d/yyyy h:mm > > B1: (end time) 3/30/08 13:00 Custom format: m/d/yyyy h:mm > > C1: (duration) formula B1-A1 - value = 76:25 Custom format: [h]:mm (this > > seemed to be the only format that returned the duration in hours > > appropriately) > > > > I average the values in column C, and end up with a number greater than my > > longest duration. Custom format of [h]:mm or m/d/yyyy h:mm do not provide > > correct results. Suggestions are greatly appreciated. > > THANX! > > -- > > Dave Peterson >

 Index ‹ Excel ‹ Excel