A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to calculate days:Hours:Minutes:



 
 
Thread Tools Display Modes
  #21  
Old August 1st, 2004, 03:36 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

It could be rewritten to handle a range as an argument. I didn't spend the
time on it because I wasn't sure it you needed it.

If you have to average several hundred cells, then I don't think you're going
to get a formula to do it. It would probably be much too long.

It you are interested in an expanded VBA function, let me know. But before I
spend the time on it, I need some pretty specific information about where the
referenced cells are. Are they all in one row or one column? Or a mix of, say,
100 cells in one column, 25 in another, plus a few scattered cells? Will there
ever be any cells that in fact DON'T contain a date/time in the format you
show? Does the routine need to trap out blank cells, cells containing error
values, and cells containing numbers?


On Sat, 31 Jul 2004 08:05:01 -0700, Kurewe
wrote:

Myrna,

Thanks for the input.

Although handling a VBA solution wouldn't be an issue, I neglected to say

that there could be anywhere between 500 to 1000 referenced cells. If I have
to specify each cell individually, this solution would not be a viable one.


"Myrna Larson" wrote:

Can you handle a VBA solution? Go to the VBA editor, select your workbook

over
in the upper left hand pane, go to the Insert menu, and click on Module. In
the blank code pane that appears on the right, paste the following code --
what's between the lines of tildes.

If you average only two times, the VBA code will most likely be slower than
the formula you eventually come up with. But if you need to average more

than
2 times, this function will definitely be easier to use than the

corresponding
formula. The result is in the same format as the inputs.

Two caveats:

(1) It *requires* the commas separating the 3 parts of the text.

(2) The cells to be averaged must be specified individually, i.e.

=AverageTime(A1,A2,A3,A4,A5)

If you write the formula as

=AverageTime(A1:A5)

it won't work. The result will be #VALUE!

Please let me know if this is workable.

'~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Base 0

Function AverageTime(ParamArray Durations() As Variant) As String
Dim i As Long
Dim N As Long
Dim Total As Double

N = UBound(Durations)
For i = 0 To N
Total = Total + ConvertToDays(Durations(i))
Next i

AverageTime = ConvertToText(Total / (N + 1))

End Function

Private Function ConvertToDays(vText As Variant) As Double
Dim Divisors As Variant
Dim i As Long
Dim N As Long
Dim Total As Double
Dim W() As String

Divisors = Array(1, 24, 1440)

W = Split(vText, ",")
N = UBound(W())
If N 2 Then N = 2

For i = 0 To N
Total = Total + Val(W(i)) / Divisors(i)
Next i

ConvertToDays = Total

End Function

Private Function ConvertToText(D As Double) As String
ConvertToText = Format$(Fix(D)) & " Days, " _
& Format$(Hour(D)) & " Hrs, " _
& Format$(Minute(D)) & " Min"
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~


On Fri, 30 Jul 2004 18:15:01 -0700, Kurewe


wrote:

JE McGimpsey,

Thank you for the input.

I tried that, using the same examples and the results was "#VALUE!"

Any Ideas?

"JE McGimpsey" wrote:

One way:

Assuming that the days and times are not always 2 digits each:

=TEXT(INT(AVERAGE(LEFT(A1,FIND("Days",A1)-1) + TIME(TRIM(MID(A1,
FIND(",",A1)+1, 3)), TRIM(MID(A1, FIND("Hr,",A1)+3, 3)),0), LEFT(A2,
FIND("Days",A2)-1) + TIME(TRIM(MID(A2, FIND(",",A2)+1,3)), TRIM(MID(A2,
FIND("Hr,",A2)+3,3)),0))), "0_""Days,_""") & TEXT(MOD(AVERAGE(LEFT(A1,
FIND("Days",A1)-1) + TIME(TRIM(MID(A1,FIND(",",A1)+1,3)), TRIM(MID(A1,
FIND("Hr,",A1)+3,3)),0), LEFT(A2, FIND("Days",A2)-1) + TIME(TRIM(MID(A2,
FIND(",",A2)+1,3)),TRIM(MID(A2, FIND("Hr,",A2)+3,3)), 0)),1),
"hh""_Hr,_""mm""_Min""")


I'm sure this is not optimal.




In article ,
Kurewe wrote:

Greetings,

I am working on a spreadsheet that performs several calculations on

the
data
that is provided in another spreadsheet. The basic function of my

spreadsheet
is to make it easy for the recipients to understand the data presented

to
them. Simple enough.

However, one of the sets of data is "turn around time". This data

represents
the time the file is created to the time the file is closed. It is

provided
to me in the following format: "66 Days, 23 Hr, 11Min".

I am trying to take that data and find the average "turn around time"

for
all
the files referenced in this spreadsheet. I have tried, based on the

example
above, to strip the text portions (" Days, " and " Hr, " and "min")

and
leave
it as 66:23:11. As I quickly figured out, this defaults to hh:mm:ss.

No
matter what I have tried, I can not get it to represent dd:hh:mm.

Is there a way to calcualte the following?

A1 = 66 Days, 23 Hr, 11Min
A2 = 66 Days, 23 Hr, 11Min

A3=sum(A1:A2)/2
(which would of course equal "66 Days, 23 Hr, 11Min")




  #22  
Old August 1st, 2004, 03:50 AM
Daniel.M
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

Hi,

Just to clarify what you said in regards to being able to use
'1 Days, 2 Hrs, 23 Min' the same as '01 Days, 02 Hrs, 23 Min'...
The formula would have to be greatly modified to take into
account those entries which have a single digit (1) as opposed to two digits

(01)?


If you still insist in a formula solution:

=INT(AVERAGE(MMULT(SUBSTITUTE(MID(A1:A2,SEARCH({"d ays","hr","min"},
A1)-3,3),",","")/{1,24,1440},{1;1;1})))& "_Days,_" &
TEXT(AVERAGE(MMULT(SUBSTITUTE(MID(A1:A2,SEARCH({"d ays","hr","min"},
A1)-3,3),",","")/{1,24,1440},{1;1;1})),"hh""_Hr,_""mm""_Min""")

Regards,

Daniel M.



  #23  
Old August 1st, 2004, 05:05 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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

'~~~~~~~~~~~~~~~~~~~~~~~~~

  #24  
Old August 1st, 2004, 05:09 AM
Myrna Larson
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

NOW she tells us that she has to do 500-1000 cells! Do you have a formula that
will handle that vbg ?


On Sat, 31 Jul 2004 22:50:52 -0400, "Daniel.M"
wrote:

Hi,

Just to clarify what you said in regards to being able to use
'1 Days, 2 Hrs, 23 Min' the same as '01 Days, 02 Hrs, 23 Min'...
The formula would have to be greatly modified to take into
account those entries which have a single digit (1) as opposed to two

digits
(01)?


If you still insist in a formula solution:

=INT(AVERAGE(MMULT(SUBSTITUTE(MID(A1:A2,SEARCH({" days","hr","min"},
A1)-3,3),",","")/{1,24,1440},{1;1;1})))& "_Days,_" &
TEXT(AVERAGE(MMULT(SUBSTITUTE(MID(A1:A2,SEARCH({" days","hr","min"},
A1)-3,3),",","")/{1,24,1440},{1;1;1})),"hh""_Hr,_""mm""_Min""")

Regards,

Daniel M.



  #25  
Old August 1st, 2004, 06:13 AM
Daniel.M
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

Previous one had a couple of errors. Try this one instead:

=INT(AVERAGE(MMULT(SUBSTITUTE(MID(" " &A1:A2,SEARCH({"days","hr","min"},
" " &A1:A2)-3,3),",","")/{1,24,1440},{1;1;1})))& "_Days,_" &
TEXT(AVERAGE(MMULT(SUBSTITUTE(MID(" " &A1:A2,SEARCH({"days","hr","min"},
" " &A1:A2)-3,3),",","")/{1,24,1440},{1;1;1})),"hh""_Hr,_""mm""_Min""")

Regards,

Daniel M.


  #26  
Old August 1st, 2004, 09:15 PM
Kurewe
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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

  #27  
Old August 1st, 2004, 09:23 PM
Kurewe
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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

"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

  #28  
Old August 1st, 2004, 09:47 PM
Kurewe
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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

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

'~~~~~~~~~~~~~~~~~~~~~~~~~


  #29  
Old August 1st, 2004, 11:09 PM
Dana DeLouis
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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



  #30  
Old August 1st, 2004, 11:19 PM
Myrna Larson
external usenet poster
 
Posts: n/a
Default How to calculate days:Hours:Minutes:

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 B127 -- 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(D127)/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


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

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

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

'~~~~~~~~~~~~~~~~~~~~~~~~~



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to calculate time difference? barton General Discussion 4 July 13th, 2004 05:49 AM
How to calculate time difference? barton General Discussion 0 July 12th, 2004 06:31 AM
Visual Basic....Calculate Button Don Guillett Worksheet Functions 3 April 2nd, 2004 05:24 PM


All times are GMT +1. The time now is 10:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.