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 |
#11
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hi Kurewe!
I think that your best bet may be to use Data Text to Columns on your data. You'll need to set up 6 empty columns to the right of your data. Then select your data Data Text to columns Delimited Use a space as the delimiter Add Other M as an additional delimiter Next Finish That gives you your numerics in columns A C and E Thereafter, it's easy to derive a formula to do what you want. Send a sample workbook if you like and I'll do it. On limited test data it appears to work OK. It certainly beats the complicated formulas although maybe a subroutine will be a better solution. -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#12
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hi,
So far, your second option appears to be the most viable. Thank you very much! You're welcome. 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? My formulae would need some 'serious' changes to be able to handle 1, 2 or 3 characters numeric. That's an area where VBA does the trick. 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. It does take time to do the edits if you do them 'manualy'. But, Myrna already provided you with a VBA function (in her solution) called ConvertToDays(). Make it public (instead of private) and you could use it directly in your spreadsheet or inside another function. Example, in B1: =ConvertToDays(A1) Then, you do your averages the usual way, =Average(B1:B6) or encapsulate the result in the other function Myrna wrote: =ConvertToText(Average(B1:B6)) One other way : You could also alter the AverageTime() function she provided to accept a Range as a parameter and call it directly : =AverageTime2(A1:A6) Function AverageTime2(Rng As Range) As String Dim i As Long Dim j As Long Dim TheTime As Variant j = Rng.Cells.Count ReDim TheTime(1 To j) For i = 1 To j TheTime(i) = ConvertToDays(Rng(i)) Next i AverageTime2 = ConvertToText(Application.WorksheetFunction.Averag e(TheTime)) End Function Regards, Daniel M. |
#13
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hello Norman,
Thanks for the input. I understand what you mean. The only problem I have is the creation of more cells or possibly another sheet to accomodate the end result. Looking at the solution that Daniel provided, can you think of a way to allow for single digit entries? (i.e. '1 Days, 2 Hours, 23 Min' which doesn't work with his formula as opposed to '01 Days, 02 Hours, 23 Min' which does work with his formula) =INT(AVERAGE(MMULT(MID(A1:A27,SEARCH({"days","hr", "min"},A1)-3,3)/{1,24,1440},{1;1;1})))& " Days, " &TEXT(AVERAGE(MMULT(MID(A1:A27,SEARCH({"days","hr" ,"min"},A1)-3,3)/{1,24,1440},{1;1;1})),"hh"" Hr, ""mm"" Min""") "Norman Harker" wrote: Hi Kurewe! I think that your best bet may be to use Data Text to Columns on your data. You'll need to set up 6 empty columns to the right of your data. Then select your data Data Text to columns Delimited Use a space as the delimiter Add Other M as an additional delimiter Next Finish That gives you your numerics in columns A C and E Thereafter, it's easy to derive a formula to do what you want. Send a sample workbook if you like and I'll do it. On limited test data it appears to work OK. It certainly beats the complicated formulas although maybe a subroutine will be a better solution. -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#14
|
|||
|
|||
How to calculate days:Hours:Minutes:
Daniel,
I believe the VBA method would create problems in the sense that I currently have no understanding of how it works. I've amazed myself with some of the things I have been able to accomplish with my novice Excel abilities. But, that has me entirely lost at the moment. 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)? "Daniel.M" wrote: Hi, So far, your second option appears to be the most viable. Thank you very much! You're welcome. 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? My formulae would need some 'serious' changes to be able to handle 1, 2 or 3 characters numeric. That's an area where VBA does the trick. 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. It does take time to do the edits if you do them 'manualy'. But, Myrna already provided you with a VBA function (in her solution) called ConvertToDays(). Make it public (instead of private) and you could use it directly in your spreadsheet or inside another function. Example, in B1: =ConvertToDays(A1) Then, you do your averages the usual way, =Average(B1:B6) or encapsulate the result in the other function Myrna wrote: =ConvertToText(Average(B1:B6)) One other way : You could also alter the AverageTime() function she provided to accept a Range as a parameter and call it directly : =AverageTime2(A1:A6) Function AverageTime2(Rng As Range) As String Dim i As Long Dim j As Long Dim TheTime As Variant j = Rng.Cells.Count ReDim TheTime(1 To j) For i = 1 To j TheTime(i) = ConvertToDays(Rng(i)) Next i AverageTime2 = ConvertToText(Application.WorksheetFunction.Averag e(TheTime)) End Function Regards, Daniel M. |
#15
|
|||
|
|||
How to calculate days:Hours:Minutes:
On Fri, 30 Jul 2004 16:17:02 -0700, 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") It's hard to know from what you've posted so far if all the variables are included. And what kinds of errors might be in the data. And also whether you want a string output, or some number upon which you can do further manipulations. For example, the below VBA routine will compute an average of the times and output the result as decimal days. (e.g. 66.5 would be equivalent to 66 days, 12 hours, 0 minutes). Unfortunately, I don't believe that a number of days greater than 31 can be formatted specifically. So to express the result the same as the input would require outputting the result as a string, or leaving it as decimal and leaving the formatting to you. That would leave you with the option of a usable value. Again, not knowing any variability in the output format, I did not do extensive error testing. But changes may need to be made if the format is not precisely as you indicate. If the range to be averaged is not contiguous, or if there are included blanks, the UDF will also need to be modified. But maybe this will give you a start: ================================================ Option Explicit Function AverageTimes(rg As Range) As Double Dim SumTimes As Double Dim Days As Long, Tm As Double Dim c As Range Dim Temp For Each c In rg Temp = Replace(c.Text, " ", "") Temp = Replace(Temp, ",", "") Days = Left(Temp, InStr(1, Temp, "D") - 1) Temp = Replace(Temp, Days & "Days", "") Temp = Replace(Temp, "Hr", ":") Temp = Replace(Temp, "Min", "") Tm = TimeValue(Temp) SumTimes = SumTimes + Days + Tm Next c AverageTimes = SumTimes / rg.Count End Function ================================= --ron |
#16
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hello Ron,
Actually, for the most part, all the variables are there. The data format will always be formatted as 66 Days, 23 Hr, 11Min or 1 Days, 2 Hr, 11Min. This will not vary from either of those. (difference being a 1 digit or 2 digit representation of Days, Hr or Min in any combination) "Ron Rosenfeld" wrote: On Fri, 30 Jul 2004 16:17:02 -0700, 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") It's hard to know from what you've posted so far if all the variables are included. And what kinds of errors might be in the data. And also whether you want a string output, or some number upon which you can do further manipulations. For example, the below VBA routine will compute an average of the times and output the result as decimal days. (e.g. 66.5 would be equivalent to 66 days, 12 hours, 0 minutes). Unfortunately, I don't believe that a number of days greater than 31 can be formatted specifically. So to express the result the same as the input would require outputting the result as a string, or leaving it as decimal and leaving the formatting to you. That would leave you with the option of a usable value. Again, not knowing any variability in the output format, I did not do extensive error testing. But changes may need to be made if the format is not precisely as you indicate. If the range to be averaged is not contiguous, or if there are included blanks, the UDF will also need to be modified. But maybe this will give you a start: ================================================ Option Explicit Function AverageTimes(rg As Range) As Double Dim SumTimes As Double Dim Days As Long, Tm As Double Dim c As Range Dim Temp For Each c In rg Temp = Replace(c.Text, " ", "") Temp = Replace(Temp, ",", "") Days = Left(Temp, InStr(1, Temp, "D") - 1) Temp = Replace(Temp, Days & "Days", "") Temp = Replace(Temp, "Hr", ":") Temp = Replace(Temp, "Min", "") Tm = TimeValue(Temp) SumTimes = SumTimes + Days + Tm Next c AverageTimes = SumTimes / rg.Count End Function ================================= --ron |
#17
|
|||
|
|||
How to calculate days:Hours:Minutes:
Hi Kurewe!
Once you have converted the data, you can delete all but one of the helper columns. The one you keep (after copy paste special values) is the one with the durations in a decimal number format. (eg 55.95765). And that column can be hidden and / or put somewhere out of the way. -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#18
|
|||
|
|||
How to calculate days:Hours:Minutes:
On Sat, 31 Jul 2004 11:35:01 -0700, Kurewe
wrote: Hello Ron, Actually, for the most part, all the variables are there. The data format will always be formatted as 66 Days, 23 Hr, 11Min or 1 Days, 2 Hr, 11Min. This will not vary from either of those. (difference being a 1 digit or 2 digit representation of Days, Hr or Min in any combination) What about my other caveats? Did you try my solution? Did it work for you? If not, what were the issues? --ron |
#19
|
|||
|
|||
How to calculate days:Hours:Minutes:
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. "Ron Rosenfeld" wrote: On Sat, 31 Jul 2004 11:35:01 -0700, Kurewe wrote: Hello Ron, Actually, for the most part, all the variables are there. The data format will always be formatted as 66 Days, 23 Hr, 11Min or 1 Days, 2 Hr, 11Min. This will not vary from either of those. (difference being a 1 digit or 2 digit representation of Days, Hr or Min in any combination) What about my other caveats? Did you try my solution? Did it work for you? If not, what were the issues? --ron |
#20
|
|||
|
|||
How to calculate days:Hours:Minutes:
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 |
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 |