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!

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.



Robin wrote:
>
> 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

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










"Dave Peterson" wrote:

> 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.
>
>
>
> Robin wrote:
> >
> > 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
>