Function or macro to convert string with weeks, days, hours, minutes to Hours

[kknb0800]

Hi,

I would like some help coming up with a function or macro that will enable me to convert the below estimates stored as strings in excel into hours. I also want to avoid using text to columns.

Original Estimate
1 week, 6 hours
1 week, 1 day, 6 hours
2 hours, 35 minutes
2 weeks
1 hour, 1 minute

The results should be:
Hours
174
198
2.58
336
1.02

I'm really stumped and would appreciate any help.

Thanks!

[StevenM]

Place the following code in a standard module, and use like a user defined function.

Function Convert2Hours(rg As Range) As Double
    Dim sArray() As String
    Dim i As Long
    Dim nValue As Long
    Dim dbResult As Double
    
    sArray = Split(rg.Text, ",")
    For i = LBound(sArray) To UBound(sArray)
        nValue = CLng(Val(sArray(i)))
        If InStr(1, sArray(i), "minute", vbTextCompare) > 0 Then
            dbResult = dbResult + nValue / 60
        ElseIf InStr(1, sArray(i), "hour", vbTextCompare) > 0 Then
            dbResult = dbResult + nValue
        ElseIf InStr(1, sArray(i), "day", vbTextCompare) > 0 Then
            dbResult = dbResult + nValue * 24
        ElseIf InStr(1, sArray(i), "week", vbTextCompare) > 0 Then
            dbResult = dbResult + nValue * (24 * 7)
        End If
    Next i
    Convert2Hours = dbResult
End Function

[kknb0800]

This is exactly what I was looking for.

Thanks for the quick reply!!