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

[Bigskenney]

I have a problem that if able to use software that I am more accusto
to, like MatLab, I would be able to fix fairly easily. Unfortunately
am only able to use Excel for this and I am still learning the languag
and capabilities of Excel. I have a text field that is representativ
of outage time that is filled with items like this: 1day 9hrs 35min
53seconds, 15hrs 20mins 50seconds, and 11days 23hrs 59mins 14seconds.
have been able to use Substitute to put this in a ':' for the strin
portions, 1:9:35:53, But this is still not able to be sorted longest t
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
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 a
simple as this, I just am missing the proper language to get it done
Thanks for the help

 

[Claus Busch]

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

 

[Bigskenney]

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

 

[Ron Rosenfeld]

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
==============================

 

[Ron Rosenfeld]

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

 

[Bigskenney]

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

 

[Ron Rosenfeld]

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.