Using preceding 0's - 23.71239712 or when formatted 23:17:05
Not using preceding 0's - 23.9715535 or when formatted 23:23:19

I can't add much. The following is similar to Ron's code, and returned
23:19.
I don't know how you want to format it, so I just included something a
little different.

returns:
23 Days 23 Hours 19 Minutes

Function AverageTimes(Rng As Range) As String
Dim v
Dim Cell
Dim Total As Double
Const Fmt As String = "y ""Days "" hh ""Hours"" mm ""Minutes"" "

For Each Cell In Rng
v = Split(Cell, ",")
Total = Total + TimeSerial(24 * Val(v(0)) + Val(v(1)), Val(v(2)), 0)
Next Cell

Total = Total / Rng.Cells.Count
AverageTimes = Format(Total + 1, Fmt)
End Function

HTH
Dana DeLouis


"Kurewe" wrote in message
...
Ron,

Thank you for the explanation. The UDF works. Sorry to have been a pain. I
was able to successfully format it as dd:hh:mm and it produced the same
numbers as the formula that Daniel provided. So far, So good.

Now, the issue I was having with Daniel's formula is that it does not work
with single numerics inthe entries. The report that generates the data
leaves of the preceeding 0 for those entries which contain 0 through 9.
(i.e. 5 instead of 05). I have been unable to get his formula to account for
that.

The UDF that you provided works for both scenarios. But, the result is
different. Given the data below, the results a
Using preceeding 0's - 23.71239712 or when formatted 23:17:05
Not using preceeding 0's - 23.9715535 or when formatted 23:23:19

Data Set without preceeding 0's
66 Days, 23 Hr, 11Min
0 Days, 0 Hr, 3Min
44 Days, 23 Hr, 6Min
10 Days, 19 Hr, 51Min
12 Days, 1 Hr, 17Min
33 Days, 1 Hr, 21Min
60 Days, 23 Hr, 4Min
0 Days, 0 Hr, 7Min
62 Days, 19 Hr, 19Min
1 Days, 20 Hr, 26Min
4 Days, 4 Hr, 52Min
77 Days, 16 Hr, 58Min
13 Days, 19 Hr, 21Min
24 Days, 1 Hr, 4Min
24 Days, 1 Hr, 6Min
48 Days, 19 Hr, 40Min
1 Days, 2 Hr, 35Min
0 Days, 0 Hr, 5Min
0 Days, 0 Hr, 7Min
0 Days, 0 Hr, 13Min
65 Days, 3 Hr, 31Min
7 Days, 4 Hr, 11Min
0 Days, 0 Hr, 10Min
0 Days, 19 Hr, 20Min
0 Days, 0 Hr, 28Min
55 Days, 18 Hr, 54Min
31 Days, 1 Hr, 14Min

"Ron Rosenfeld" wrote:

On Sat, 31 Jul 2004 13:59:01 -0700, Kurewe

wrote:

Ron,

I have tried using the VBA routine and I get as far as inserting the
module and pasting in the code. However, I have no experience with VBA and
can get no further at the moment.

I unfortunately do not know if your suggestion will work.

To use the UDF:

1. Open your worksheet with the turn around time data
2. alt-F11 opens the Visual Basic Editor
3. Ensure your project is highlighted in the project explorer.
4. Insert/Module and then paste in the code from my previous message.

5. Then on the worksheet itself, enter a formula of the type

=AverageTimes(A2:A100)
(replace A2:A100 with the range where your data exists).

then Enter

If it works, the correct answer should appear.


--ron