CODEWITHSUNDEEP
excel
Dotnet coder
Dotnet coder

Reputation: 41

Excel formula to convert string containing weeks, days, hours to hours

I usually get excel reports for tracking user effort from a ticketing tool in the below format:

3d 5h
1w 2d 6h

The above strings are represented in w-weeks, h-hours, d-days. Basically, 1 day here represents 8 hours and 1 week represents 5 days. In the above example, 3d 5h should return "29".

I am looking for an excel formula to convert such values to hours only format.

Thanks in advance.

Upvotes: 0

Views: 567

Answers (3)

pnuts
pnuts

Reputation: 59442

With a formula:

="="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"w","*40+"),"d","*8+"),"h","")," ","")

Then Copy, Paste Special, Values over the top and Replace = with =.

Upvotes: 0

xidgel
xidgel

Reputation: 3145

Here's one way:

Public Function GetTotalHours(r As Range) As Double
    Dim s As String
    s = r.Value2
    s = Replace(s, "w", "*40")
    s = Replace(s, "d", "*8")
    s = Replace(s, "h", "*1")
    s = Replace(s, " ", "+")
    GetTotalHours = Evaluate(s)
End Function

Hope that helps

Upvotes: 1

Sorceri
Sorceri

Reputation: 8033

You would be better off creating a UDF to perform this.

Function GetTotalHours(hourValues As Range) As Long
Dim hourPartsArray
Dim totalHours As Long
Dim i As Integer
    hourPartsArray = Split(hourValues.Value2, " ")
    For i = 0 To UBound(hourPartsArray)
    Dim tempValue
        If InStr(1, hourPartsArray(i), "w") > 0 Then
            tempValue = Replace(hourPartsArray(i), "w", "")
            totalHours = totalHours + tempValue * 5 * 8
        End If
        If InStr(1, hourPartsArray(i), "d") > 0 Then
            tempValue = Replace(hourPartsArray(i), "d", "")
            totalHours = totalHours + tempValue * 8

        End If
        If InStr(1, hourPartsArray(i), "h") > 0 Then
            tempValue = Replace(hourPartsArray(i), "h", "")
            totalHours = totalHours + tempValue

        End If
    Next i
    GetTotalHours = totalHours
End Function

and to use

=GetTotalHours(A1)

Upvotes: 0

Related Questions