Averaging Duration of Time: Duration Longer than 24hr

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin - 10 Apr 2008 23:13 GMT

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!

Reply to this Message

Dave Peterson - 11 Apr 2008 00:05 GMT

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.
[quoted text clipped - 14 lines]
> correct results. Suggestions are greatly appreciated.
> THANX!

Signature

Dave Peterson

Reply to this Message

Robin - 11 Apr 2008 00:55 GMT

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.
[quoted text clipped - 17 lines]
> > correct results. Suggestions are greatly appreciated.
> > THANX!

Reply to this Message