Changing Hours, minutes, seconds to hours

[Guzzlr]

Hello
I am having trouble with vba to change:
2 Hour - 55 Minutes - 22 Seconds to an hour decimal, which equals 2.92 hours.

Any help is greatly appreciated.
Thank you

 

[Rick Rothstein]

I think this will work...

=24*SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Hour",""),"Minute",""),"Second",""),"s",""),"-",""))," ",":")

 

[Guzzlr]

Will there always be an hour, minute, and second?

So if it was 55 minutes, would it say this:
0 hours - 55 minutes - 0 seconds

Not all cells will always have Hours, Minutes, Seconds.
Some may have only Minutes, and Seconds.

So, in your example. there may be a cell with only 55 Minutes showing. Therefore it only say 55 Minutes.
There are 3 principle designators of Hours, Minutes, Seconds. and they are all spelled the same (no abbreviations) and always capital.
To answer Alex question, there will not be a "0" for a second digit like xx hours. it will be either 5 Hours or 10 hours for example.
So the original statement of 2 Hours 55 Minutes 22 Seconds will never be shown as 02 Hours 55 Minutes 22 Seconds.

 

[Alex Blakenburg]

1) Do you want VBA or a formula ?
2) Show us a sample of your data including Row & Column references, and showing all possible combinations.

 

[Guzzlr]

1) Do you want VBA or a formula ?
2) Show us a sample of your data including Row & Column references, and showing all possible combinations.

Here is a sample. The data to be converted is in Column L. The Header of "Est Duration" is L5.
This particular data set goes to row 2851.
VBA or Formula question....VBA would be good.
Thank you for all the help

 

[Guzzlr]

Here is a sample. The data to be converted is in Column L. The Header of "Est Duration" is L5.
This particular data set goes to row 2851.
VBA or Formula question....VBA would be good.
Thank you for all the help




View attachment 108683

Does this help any?
Thanks

 

[Guzzlr]

I think this will work...

=24*SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Hour",""),"Minute",""),"Second",""),"s",""),"-",""))," ",":")

So this will work, as long as there are Hours, Minutes and Seconds. However will not work if, for example, there is only say...48 Minutes, with no hours or seconds.
Thanks for the help

 

[Cubist]

Try this UDF.

Function ConvertTimeStringToHours(inputString As String) As Double
    Dim hours As Double
    Dim minutes As Double
    Dim seconds As Double
    
    Dim parts() As String
    parts = Split(inputString, " ")
    
    Dim i As Integer
    For i = LBound(parts) To UBound(parts)
        If UCase(parts(i)) = "HOUR" Then
            hours = Val(parts(i - 1))
        ElseIf UCase(parts(i)) = "MINUTES" Then
            minutes = Val(parts(i - 1))
        ElseIf UCase(parts(i)) = "SECONDS" Then
            seconds = Val(parts(i - 1))
        End If
    Next i
    
    ConvertTimeStringToHours = hours + minutes / 60 + seconds / 3600
End Function

 

[Rick Rothstein]

Try this UDF.

    parts = Split(inputString, " ")
  
    Dim i As Integer
    For i = LBound(parts) To UBound(parts)

Just noting that the lower bound for an array created by the Split function is always 0.

 

[Rick Rothstein]

So this will work, as long as there are Hours, Minutes and Seconds. However will not work if, for example, there is only say...48 Minutes, with no hours or seconds.
Thanks for the help

I know... I posted that before you told us that your values did not always contain all three parts of the time. By the way, are you still using xl2016 or have you upgraded since putting that in your profile?

 

[Alex Blakenburg]

Here is a sample. The data to be converted is in Column L. The Header of "Est Duration" is L5.
This particular data set goes to row 2851.
VBA or Formula question....VBA would be good.

Cubist has already provided a Function version but since you have provided information for running a macro try this on a copy of your workbook.

At this stage it is outputting to the next empty column in the spreadsheet.
If you have validated the output and want it to overwrite column L that is a simple modification.

Sub ConvertStringToTime()
    
    Dim ws As Worksheet
    Dim rngTimeTxt As Range, arr As Variant
    Dim rngOut As Range
    Dim splitTime As Variant, partTime As String
    Dim rowHdg As Long, i As Long, j As Long
    Dim hrs As Long
    Dim mins As Long
    Dim secs As Long
    
    Set ws = ActiveSheet
    rowHdg = 5
    With ws
        Set rngTimeTxt = .Range(.Cells(rowHdg + 1, "L"), .Cells(Rows.Count, "L").End(xlUp))
        arr = rngTimeTxt.Value
    End With
    
    Set rngOut = ws.Cells(rowHdg, Columns.Count).End(xlToLeft).Offset(1, 1)
    
    For i = 1 To UBound(arr)
        hrs = 0
        mins = 0
        secs = 0
        splitTime = Split(Trim(arr(i, 1)), " ")
        For j = 0 To UBound(splitTime)
            partTime = splitTime(j)
            Select Case True
                Case UCase(partTime) Like "HOUR*"
                    hrs = Val(splitTime(j - 1))
                Case UCase(partTime) Like "MINUTE*"
                    mins = Val(splitTime(j - 1))
                Case UCase(partTime) Like "SECOND*"
                    secs = Val(splitTime(j - 1))
                Case Else
                    ' do nothing
            End Select
        Next j
        arr(i, 1) = 24 * TimeSerial(hrs, mins, secs)
    Next i
    
    'rngOut.Offset(-1).Value = "Est Duration (hrs)"         <--- Leave heading for now, easier to rerun
    rngOut.Resize(UBound(arr)) = arr
End Sub