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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |