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 |
#7
|
|||
|
|||
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") |
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 |