Looking for an If then formula

[RayKIII]

I have a worksheet with departments, categories and times. The times are currently in minute, hour, day and week format. I am looking for a formula that says if the time in column e is 15m then column f shows .25 or if the time in column e is 1h then column f is 1 or if time in column e is 1w2d4h15m then time in column f is 60.25.

I am also looking to see if I can set up some formulas that will add all the like rows together. Something like, if column b is accounting then take total from column f and add them all together. And if that is possible can you make is so if column c is accounting and column d is firewall then take column f and add them all together.

Is that possible or am I asking excel to do to much?

 

[CharlesChuckieCharles]

Can parts of 1w2d4h15m be missing

3d7h for 3days 7 hours

 

[RayKIII]

Yes, that is correct. If there are no minutes logged then it will be left off. So it could be 3d30m or 4d or 1w30m and so on.

 

[CharlesChuckieCharles]

This function should work

in Vba

Inset a module

and paste the following

Function MyTurnTextintoTime(sRangeContainingText As Range) As Double
Dim stemp As String
Dim sValueStr As String
Dim lValues(4) As Long
Dim x As Long
Dim y As Long
' expecting  w week  d day  h  hour  m minute   ONLY
stemp = UCase(Trim(sRangeContainingText))
sValueStr = ""
For x = 0 To 3
    lValues(x) = 0
Next x
'read from right to left
For x = Len(stemp) To 1 Step -1
       'Debug.Print Mid$(stemp, x, 1)
       Select Case Mid$(stemp, x, 1)
       Case "M"
            If sValueStr <> "" Then
              lValues(y) = Val(sValueStr)
              sValueStr = ""
            End If
            y = 3
       Case "H"
            If sValueStr <> "" Then
              lValues(y) = Val(sValueStr)
              sValueStr = ""
            End If
            y = 2
       Case "D"
            If sValueStr <> "" Then
              lValues(y) = Val(sValueStr)
              sValueStr = ""
            End If
            y = 1
       Case "W"
            If sValueStr <> "" Then
              lValues(y) = Val(sValueStr)
              sValueStr = ""
            End If
            y = 0
       Case Else
            sValueStr = Mid$(stemp, x, 1) & sValueStr
    End Select
Next x
lValues(y) = Val(sValueStr)
MyTurnTextintoTime = lValues(0) * 40 + lValues(1) * 8 + lValues(2) + IIf(lValues(3) = 0, 0, (lValues(3) / 60))
End Function

Use in spreadsheet like this


=MyTurnTextintoTime(E13)


where E13 contains your time string
i.e 2d15m

assumes 8 hour day

 

[RayKIII]

Thank you, I will give this a shot!

 

[RayKIII]

This worked perfectly, thank you very much!!