A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to calculate days:Hours:Minutes:



 
 
Thread Tools Display Modes
  #11  
Old July 31st, 2004, 06:13 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 06:25 PM
Daniel.M
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 07:09 PM
Kurewe
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 07:15 PM
Kurewe
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 07:22 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 07:35 PM
Kurewe
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 07:37 PM
Norman Harker
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 08:31 PM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default 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  
Old July 31st, 2004, 09:59 PM
Kurewe
external usenet poster
 
Posts: n/a
Default 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  
Old August 1st, 2004, 02:01 AM
Ron Rosenfeld
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 07:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.