Text Dates/times to d:hh:mm:ss
 

I have a problem that if able to use software that I am more accustom to, like MatLab, I would be able to fix fairly easily. Unfortunately I am only able to use Excel for this and I am still learning the language and capabilities of Excel. I have a text field that is representative of outage time that is filled with items like this: 1day 9hrs 35mins 53seconds, 15hrs 20mins 50seconds, and 11days 23hrs 59mins 14seconds. I have been able to use Substitute to put this in a ':' for the string portions, 1:9:35:53, But this is still not able to be sorted longest to shortest because if there is no 'day' to substitute it is left blank, 15:20:50. I have tried formating, which didn't help, and I can't find a way to use an ELSE statement to substitute a '00' if the 'day'/'days' isn't present. I would think that Excel is able to do something as simple as this, I just am missing the proper language to get it done. Thanks for the help.

Text Dates/times to d:hh:mm:ss
 

Hi,

Am Wed, 15 May 2013 19:57:51 +0100 schrieb Bigskenney:

I have a problem that if able to use software that I am more accustom
to, like MatLab, I would be able to fix fairly easily. Unfortunately I
am only able to use Excel for this and I am still learning the language
and capabilities of Excel. I have a text field that is representative
of outage time that is filled with items like this: 1day 9hrs 35mins
53seconds, 15hrs 20mins 50seconds, and 11days 23hrs 59mins 14seconds. I
have been able to use Substitute to put this in a ':' for the string
portions, 1:9:35:53, But this is still not able to be sorted longest to
shortest because if there is no 'day' to substitute it is left blank,
15:20:50. I have tried formating, which didn't help, and I can't find a
way to use an ELSE statement to substitute a '00' if the 'day'/'days'
isn't present. I would think that Excel is able to do something as
simple as this, I just am missing the proper language to get it done.

substitute hrs, mins and seconds to ":".
If your values are in column A then in B1:
=IF(ISNUMBER(FIND("day",A1)),LEFT(A1,FIND("day",A1 )-1),0)+IF(ISTEXT(A1),--(RIGHT(A1,LEN(A1)-FIND(" ",A1))),A1)
and format [h]:mm:ss
or
d:hh:mm:ss


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks for the help. This works for the cells that have a day in them, but it does not on the ones that do not.

Text Dates/times to d:hh:mm:ss
 

On Wed, 15 May 2013 19:57:51 +0100, Bigskenney wrote:


I have a problem that if able to use software that I am more accustom
to, like MatLab, I would be able to fix fairly easily. Unfortunately I
am only able to use Excel for this and I am still learning the language
and capabilities of Excel. I have a text field that is representative
of outage time that is filled with items like this: 1day 9hrs 35mins
53seconds, 15hrs 20mins 50seconds, and 11days 23hrs 59mins 14seconds. I
have been able to use Substitute to put this in a ':' for the string
portions, 1:9:35:53, But this is still not able to be sorted longest to
shortest because if there is no 'day' to substitute it is left blank,
15:20:50. I have tried formating, which didn't help, and I can't find a
way to use an ELSE statement to substitute a '00' if the 'day'/'days'
isn't present. I would think that Excel is able to do something as
simple as this, I just am missing the proper language to get it done.
Thanks for the help.

I'm having a problem with your approach.
If I enter a value in a cell (Excel 2007), such as 1:9:35:53, it remains a text string and I cannot convert it to a time value. If that is truly the case, and I am not making some simple error, in order to have those values sort properly, you would need to enter it as something like 001:09:35:53. This can be done with formulas but is fairly complex.

I would suggest converting the string to an "Excel date", then formatting as you wish.
Excel stores dates as serial numbers representing days and fractions of a day generally with "1" = 1/1/1900

As far as formatting the output is concerned, although you could use a format of d:h:m:s, Excel will not display "days" with a value of greater than 31, so it is safer to format as [h]:m:s. A standalone "h" will display a maximum of 23 hrs, however the "[h]" will display up to the maximum possible in Excel.

Although possible to do this with a complex formula, it is simpler (for me) to create a User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConvertTime(A1)

in some cell.

For clarity, format the result as [h]:mm:ss

Then sort on that column.

The routine below uses regular expressions to extract the various time units from the string. Any given time unit is optional. The time unit is defined by the starting letter (d,h,m,s) so various spellings, abbreviations, plural vs singular forms should not be an issue.

====================================
Option Explicit
Function ConvertTime(s As String) As Date
Dim re As Object, sm As Object
Dim d As Double
Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "(\d+(?=\s*d\w*))?\D*(\d+(?=\s*h\w*))?\D*(\d+(?=\s *m\w*))?\D*(\d+(?=\s*s\w*))?"
.Global = True
.ignorecase = True
If .test(s) = True Then
Set sm = .Execute(s)(0).submatches
d = sm(0) + _
sm(1) / 24 + _
sm(2) / 24 / 60 + _
sm(3) / 24 / 60 / 60
ConvertTime = d
End If
End With
End Function
==============================

Text Dates/times to d:hh:mm:ss
 

On Tue, 21 May 2013 19:01:07 -0400, Ron Rosenfeld wrote:

One minor change: if there are any characters preceding the first number in the string, the UDF will not return anything. A simple fix would be to change .Pattern to:

..Pattern = "\D*(\d+(?=\s*d\w*))?\D*(\d+(?=\s*h\w*))?\D*(\d+(? =\s*m\w*))?\D*(\d+(?=\s*s\w*))?"

Thanks Ron, this has helped a lot. I didn't even think of creating my own function, now it is working.

Text Dates/times to d:hh:mm:ss
 

On Tue, 11 Jun 2013 22:07:49 +0100, Bigskenney wrote:


Thanks Ron, this has helped a lot. I didn't even think of creating my
own function, now it is working.

Glad to help. Thanks for the feedback.