Hi, MR(!) Kurewe
Sorry for the gender error. I can empathize with your reaction -- many people
for whom English is not their native language don't recognize the "Myrna" is
female. Some years ago I got a hilarious (to me) reaction when after about 3
months, a German man learned that I was a "she" rather than a "he". "Women
don't know anything about math and computers", he said.
I thought I had also explained in my 1st message how to get the code into your
workbook, but maybe not.
What I think: your first result, 23:17:05, is not correct.
To establish that, I did the following:
I pasted the values you show in your other reply (the data without leading
0's) into a worksheet.
I copied it to column B, then used data/text to columns to separate at the
comma into 3 columns. I used search & replace 4 times to remove (a) the
spaces, (b) "days", (c) "hr", (d) "min".
I ended up with all numeric data in columns B
, as evidenced by the change
from left alignment to right alignment. The original text data is in A1:A27.
The split values in B1
27 -- days in B, hours in C, minutes in D.
I put some formulas at the bottom:
A30: =AverageTime(A1:A27)
B28: =SUM(B1:B27)+SUM(C1:C27)/24+SUM(D1
27)/1440
B29: =B28/27
B30: =ConvertToText(B29) (a call to my function to format the result)
The results are, respectively:
A30: 23 Days, 23 Hrs, 19 Min
B28: 647.2319444 (i.e. decimal days, how XL stores dates internally)
B29: 23.9715535 (also decimal days)
B30: 23 Days, 23 Hrs, 19 Min (same as A30)
In F1 I put this formula and copied down through F27:
=TEXT(B1,"00")&" Days, "&TEXT(C1,"00")&" Hr, "&TEXT(D1,"00")&"Min"
This gives me data with leading 0's in F1:F27.
F30: =AverageTime(F1:F27) gives 23 Days, 23 Hrs, 19 Min
I added a new function to the code (see below), to allow you to use a
worksheet formula to just convert the text to a number. Given that, you can
put formulas on the right that convert the data in column A (or that in column
F) to a number. The results are always identical, i.e. the VBA code doesn't
depend on leading 0's.
Function ConvertToDecimal(v As String) As Double
ParseDuration v, ConvertToDecimal
End Function
If you want me to send you the workbook where I did all of this, give me your
email address. If you decide to use my code, you need a workbook like this
anyway, to satisfy yourself that the results are correct. Let me know...
Myrna Larson
Microsoft MVP, Excel
Myrna,
Thank You! Ron Rosenfeld gave me some tutoring for VBA and I tested the code.
Yours looks to be the closest to what I need. I posted replies to Daniel and
Ron regarding their solutions. Yours of course produces the same difference as
theirs. If you wouldn't mind, take a look at those replies and let me know
what you think.
Thanks again for all the help.
Myrna Larson said:
Here is modified code that will handle more than one range, i.e. you could
write
=AverageTimes(J2:J83,J90:J400,"23 Days, 5 Hr, 6Min")
If any of the arguments are not cells containing text in the format you
specified, or literal text in that format, those cells/arguments are treated
as 0. You must specify all 3 pieces, even if the number is 0, i.e. "5 Hr, 10
Min" is illegal -- it has to be "0 Days, 5 Hr, 10 Min".
But I allowed "Day" as well as "Days", it's not case-sensitive, and it's not
fussy about the number or position of spaces [e.g. you have a space before
Days and Hr, but not before "Min"])
Note that the AverageTime function converts each argument to a number, totals
and averages them, and converts the average into the above format.
In my first reply, I explained how to get the code into your workbook. Others
have provided the same information. Good luck!
'~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Base 0
Function AverageTime(ParamArray Durations() As Variant) As String
Dim C As Range
Dim Dur As Double
Dim i As Long
Dim N As Long
Dim Total As Double
N = 0
For i = 0 To UBound(Durations)
'arguments must be a range (1 or more cells) or literal text
Select Case TypeName(Durations(i))
Case "Range"
For Each C In Durations(i).Cells
'ParseDuration will return False if
'the value isn't text in the proper format
If ParseDuration(C.Value, Dur) Then
Total = Total + Dur
N = N + 1
End If
Next C
Case "String"
If ParseDuration((Durations(i)), Dur) Then
Total = Total + Dur
N = N + 1
End If
End Select
Next i
If Total > 0 And N > 0 Then
AverageTime = ConvertToText(Total / N)
Else
AverageTime = ConvertToText(0)
End If
End Function
Private Function ParseDuration(vDuration As Variant, _
nDuration As Double) As Boolean
Dim Components() As String
Dim Divisors As Variant
Dim i As Long
Dim N As Long
Dim Txt As String
nDuration = 0
ParseDuration = False
If TypeName(vDuration) <> "String" Then
Exit Function
Else
Txt = CStr(vDuration) 'put it into a string
Txt = LCase$(Txt) 'convert to lower case
Txt = Replace(Txt, " ", "") 'remove the spaces
'check for text 'Days,' 'Hr,' and ending with Min
If (Txt Like "*days,*hr,*min") = False Then
If (Txt Like "*day,*hr,*min") = False Then
Exit Function
End If
End If
End If
Divisors = Array(1, 24, 1440)
Components() = Split(vDuration, ",")
N = UBound(Components())
'require all 3 pieces to be there
If N <> 2 Then Exit Function
For i = 0 To N
nDuration = nDuration + Val(Components(i)) / Divisors(i)
Next i
ParseDuration = True
End Function
Function ConvertToText(D As Double) As String
ConvertToText = Format$(Fix(D)) & " Days, " _
& Format$(Hour(D)) & " Hrs, " _
& Format$(Minute(D)) & " Min"
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~
