Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
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.
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ============================== |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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*))?" |
#6
|
|||
|
|||
Thanks Ron, this has helped a lot. I didn't even think of creating my own function, now it is working.
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate times between dates and times in Excel | Excel Discussion (Misc queries) | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
Comparing Times in Text Boxes to Times in Cells | Excel Programming | |||
Count times between 2 times and 2 dates | Excel Programming | |||
=Text(b2-A2,"h") hours between 2 times, what do I do for 10 times | New Users to Excel |