How to calculate days:Hours:Minutes:

Hi, Dana:

I just posted a message that explains how I demonstrated that 23:17:05 isn't
correct. Must be something wrong with Ron's code, but I haven't looked at it
so find out.

Myrna Larson


On Sun, 1 Aug 2004 18:09:44 -0400, "Dana DeLouis"
wrote:

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

I think at this point it's appropriate to bite the bullet, abandon formulas,
and go with VBA. All of the proposed VBA solutions produce the same
(correct) answer.

"Kurewe" wrote in message
...
Addition to my previous response. Daniels Formula which he updated also
produces the same difference when using the entries without preceeding 0's.

On Sun, 1 Aug 2004 13:15:02 -0700, Kurewe
wrote:

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

I cannot reproduce the error you say you obtain with my UDF when using leading
zeros. I get the same, correct, result (23.9715535) regardless of whether
there are leading zeros or not in the data you posted.

So please post the data that you think is giving erroneous results with my UDF
so we can see exactly what is going on.

======================

I was able to successfully format it as dd:hh:mm

That formatting will ONLY give you a valid result IF and ONLY IF the average
number of days is 31 or less. If your average number of days is greater than
31, your result will not make sense.

So if you are going to deal with averages that are greater than 31 days, you
will need to display them either as a string, or as a decimal number.

To display it as a decimal number, format as General, or as Number with the
desired number of decimal places.

To display it as Text, a formula such as:

=TEXT(INT(averagetimes(A1:A27)),"0 ""days, """)&
TEXT(MOD(averagetimes(A1:A27),1),"h ""Hr, ""m ""Min""")


--ron

On Sun, 1 Aug 2004 18:09:44 -0400, "Dana DeLouis"
wrote:

Val(v(0)) + Val(v(1)), Val(v(2)), 0)

Neat!

I purposely did not use Split, not knowing what version the OP had; however, I
did not realize that Val would work on strings that started with numeric data.
I need to remember that one.


--ron

And in this case, the "old" way has a definite advantage over CDbl, CLng,
CInt, etc. It stops converting when it hits a character (like the D in Days)
that can't be part of a number. CDbl would give a type-mismatch error.


On Sun, 01 Aug 2004 20:15:32 -0400, Ron Rosenfeld
wrote:

On Sun, 1 Aug 2004 18:09:44 -0400, "Dana DeLouis"
wrote:

Val(v(0)) + Val(v(1)), Val(v(2)), 0)

Neat!

I purposely did not use Split, not knowing what version the OP had; however,
I
did not realize that Val would work on strings that started with numeric
data.
I need to remember that one.


--ron

Just to share... Val has the same "issue" as CDbl with the Single letters D
d E & e." However it didn't look like that was likely in this case. Just
something to keep in mind. :)

Sub Demo()
Dim s1 As String
Dim s2 As String

s1 = "123D3"
s2 = "123E3"

Debug.Print Val(s1)
Debug.Print CDbl(s1)

Debug.Print Val(s2)
Debug.Print CDbl(s2)
End Sub

Returns:
123000
123000
123000
123000

HTH
Dana DeLouis


"Myrna Larson" wrote in message
...
And in this case, the "old" way has a definite advantage over CDbl, CLng,
CInt, etc. It stops converting when it hits a character (like the D in
Days)
that can't be part of a number. CDbl would give a type-mismatch error.


On Sun, 01 Aug 2004 20:15:32 -0400, Ron Rosenfeld

wrote:

On Sun, 1 Aug 2004 18:09:44 -0400, "Dana DeLouis"
wrote:

Val(v(0)) + Val(v(1)), Val(v(2)), 0)

Neat!

I purposely did not use Split, not knowing what version the OP had;
however,
I
did not realize that Val would work on strings that started with numeric
data.
I need to remember that one.


--ron

Myrna, Daniel, Ron and Dana:

First of all, I want to thank you all for your patience and your assistance. I have learned some new things from each of you. I marked each of you suggestions as answering the question. They all Work.

I will likely be using Myrna's suggestion. The reason being, that her suggestion allows for multiple ranges (which includes ranges from other sheets within the workbook). This will help me in the long run, as the project evolves.

With regards to this last hurdle... Myrna, I believe you're right with regards to the "23 Days, 23 Hrs, 19 Min" being the proper result. I'm confused as to why and can only surmise that the added 0's somehow change the value. Based on my new found interest in VBA and

I have tested each person's suggestion with both the data sets below. Each of them provide the exact same answers as shown below.

If it helps to understand where I am coming from, I have uploaded my test workbook to my site. All four suggestions are there, with the data that I used below. I've also included my email address in the workbook if you would prefer to continue via email.
http://www.kurewe.com/book1.xls

23 Days, 17 Hrs, 5 Min 23 Days, 23 Hrs, 19 Min

With the 0's Without the 0's

66 Days, 23 Hr, 11Min 66 Days, 23 Hr, 11Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 3Min
44 Days, 23 Hr, 06Min 44 Days, 23 Hr, 6Min
10 Days, 19 Hr, 51Min 10 Days, 19 Hr, 51Min
12 Days, 01 Hr, 17Min 12 Days, 1 Hr, 17Min
33 Days, 01 Hr, 21Min 33 Days, 1 Hr, 21Min
60 Days, 23 Hr, 04Min 60 Days, 23 Hr, 4Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 7Min
62 Days, 19 Hr, 19Min 62 Days, 19 Hr, 19Min
01 Days, 20 Hr, 26Min 1 Days, 20 Hr, 26Min
04 Days, 04 Hr, 52Min 4 Days, 4 Hr, 52Min
77 Days, 16 Hr, 58Min 77 Days, 16 Hr, 58Min
13 Days, 19 Hr, 21Min 13 Days, 19 Hr, 21Min
24 Days, 01 Hr, 04Min 24 Days, 1 Hr, 4Min
24 Days, 01 Hr, 06Min 24 Days, 1 Hr, 6Min
48 Days, 19 Hr, 40Min 48 Days, 19 Hr, 40Min
01 Days, 02 Hr, 35Min 1 Days, 2 Hr, 35Min
00 Days, 00 Hr, 05Min 0 Days, 0 Hr, 5Min
00 Days, 00 Hr, 07Min 0 Days, 0 Hr, 7Min
00 Days, 00 Hr, 13Min 0 Days, 0 Hr, 13Min
65 Days, 03 Hr, 31Min 65 Days, 3 Hr, 31Min
00 Days, 04 Hr, 11Min 7 Days, 4 Hr, 11Min
00 Days, 00 Hr, 10Min 0 Days, 0 Hr, 10Min
00 Days, 19 Hr, 20Min 0 Days, 19 Hr, 20Min
00 Days, 00 Hr, 28Min 0 Days, 0 Hr, 28Min
55 Days, 18 Hr, 54Min 55 Days, 18 Hr, 54Min
31 Days, 01 Hr, 14Min 31 Days, 1 Hr, 14Min

See my response to myself at the bottom. Too many sub threads

See my response to myself at the bottom. Too many sub threads

See my response to myself at the bottom. Too many sub threads