How to calculate days:Hours:Minutes:

[Guest]

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

 

[JE McGimpsey]

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.

 

[Guest]

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?

 

[Guest]

JE McGimpsey,

Thank you for the input.

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

Any Ideas?

 

[Myrna Larson]

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
'~~~~~~~~~~~~~~~~~~~~~~~~

 

[JE McGimpsey]

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

 

[Daniel.M]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Norman Harker]

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.

 

[Daniel.M]

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.Average(TheTime))

End Function


Regards,

Daniel M.

 

[Guest]

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

 

[Guest]

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)?

 

[Ron Rosenfeld]

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

 

[Guest]

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)

 

[Norman Harker]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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