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 |
#1
|
|||
|
|||
How to calculate days:Hours:Minutes:
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") |
#2
|
|||
|
|||
How to calculate days:Hours:Minutes:
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") |
#3
|
|||
|
|||
How to calculate days:Hours:Minutes:
JE McGimpsey,
Thank you for the input. I'm not sure if I missed something. But, I copied that formula and pasted it into a cell. Then, entered the values the same as I used in the question and the result was "#VALUE!" 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") |
#4
|
|||
|
|||
How to calculate days:Hours:Minutes:
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") |
#5
|
|||
|
|||
How to calculate days:Hours:Minutes:
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") |
#6
|
|||
|
|||
How to calculate days:Hours:Minutes:
Using your data, I came up with the correct values. I've rearranged the
spacing a bit, perhaps the line-wraps screwed something up: =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""") In article , Kurewe wrote: Thank you for the input. I tried that, using the same examples and the results was "#VALUE!" Any Ideas? |
#7
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hi,
There's also: =INT(AVERAGE(SUM(MID(A1,SEARCH({"days","hr","min"} ,A1)-3,3)/{1,24,1440},{1;1;1}) , SUM(MID(A2,SEARCH({"days","hr","min"},A2)-3,3)/{1,24,1440},{1;1;1}))) & "_Days,_" & TEXT(AVERAGE(SUM(MID(A1,SEARCH({"days","hr","min"} ,A1)-3,3)/{1,24,1440},{1;1;1}) ,SUM(MID(A2,SEARCH({"days","hr","min"},A2)-3,3)/{1,24,1440},{1;1;1})), "hh""_Hr,_""mm""_Min""") In cases where you have two vertical cells (as in A1:A2) for your input, it can be abbreviated further: =INT(AVERAGE(MMULT(MID(A1:A2,SEARCH({"days","hr"," min"},A1)-3,3) /{1,24,1440},{1;1;1})))& "_Days,_" & TEXT(AVERAGE(MMULT(MID(A1:A2,SEARCH({"days","hr"," min"},A1)-3,3) /{1,24,1440},{1;1;1})),"hh""_Hr,_""mm""_Min""") But that's just an exercice for fun. I would definetively use a VBA function or have my data in 3 cells per entry (days, hours, min). Then the calcs become much easier. Regards, Daniel M. |
#8
|
|||
|
|||
How to calculate days:Hours:Minutes:
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") |
#9
|
|||
|
|||
How to calculate days:Hours:Minutes:
JE McGimpsey,
Thanks, the edited version works for average the results of the two cells and gives the proper average. Thank you very much. As I indicated to Myrna, I forgot to mention that the number of entries involved could range between 500 to 1000. Being that this formula has already gone beyond my limited abilities, I would be lost in trying to determine how to adjust it to accomodate a range of cells. Any further help would be greatly appreciated. "JE McGimpsey" wrote: Using your data, I came up with the correct values. I've rearranged the spacing a bit, perhaps the line-wraps screwed something up: =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""") In article , Kurewe wrote: Thank you for the input. I tried that, using the same examples and the results was "#VALUE!" Any Ideas? |
#10
|
|||
|
|||
How to calculate days:Hours:Minutes:
Daniel,
So far, your second option appears to be the most viable. Thank you very much! As I have replied to the other responses, there could be anywhere between 500 to 1000 cells involved. I plugged in your formula and it worked for the two cell scenario. I adjust the formula to work with a range of A1:A27 as a test and it did exactly what I needed it to. However, I receive a Value error if any of the entries contain a single digit (i.e. 1 Days, 2 Hr, 26Min). The formula does not like single digit numbers in any combination. Ideas? Unfortunately, the data I receive is in the form I provided. To further manually edit the data takes too much time. I don't believe I would be saving myself much time by splitting the Turn Around Time data into three cells to accomodate a formula. "Daniel.M" wrote: Hi, There's also: =INT(AVERAGE(SUM(MID(A1,SEARCH({"days","hr","min"} ,A1)-3,3)/{1,24,1440},{1;1;1}) , SUM(MID(A2,SEARCH({"days","hr","min"},A2)-3,3)/{1,24,1440},{1;1;1}))) & "_Days,_" & TEXT(AVERAGE(SUM(MID(A1,SEARCH({"days","hr","min"} ,A1)-3,3)/{1,24,1440},{1;1;1}) ,SUM(MID(A2,SEARCH({"days","hr","min"},A2)-3,3)/{1,24,1440},{1;1;1})), "hh""_Hr,_""mm""_Min""") In cases where you have two vertical cells (as in A1:A2) for your input, it can be abbreviated further: =INT(AVERAGE(MMULT(MID(A1:A2,SEARCH({"days","hr"," min"},A1)-3,3) /{1,24,1440},{1;1;1})))& "_Days,_" & TEXT(AVERAGE(MMULT(MID(A1:A2,SEARCH({"days","hr"," min"},A1)-3,3) /{1,24,1440},{1;1;1})),"hh""_Hr,_""mm""_Min""") But that's just an exercice for fun. I would definetively use a VBA function or have my data in 3 cells per entry (days, hours, min). Then the calcs become much easier. Regards, Daniel M. |
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 |