Averaging Duration of Time: Duration Longer than 24hr

[Robin]

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]

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!

 

[Robin]

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!