turning "1 day, 1 hour, 30 minutes" into 1530 minutes

[Joe Murphy]

I have a column of cells that contain day, hour, minute which is used to
determine how long it takes for a technician to accomplish a task in the
following format:

1 day, 1 hour, 30 minutes
1 day, 1 hour, 42 minutes
1 day, 1 hour, 52 minutes

but also includes cells that look like this:
1 hour
1 hour, 11 minutes
1 day, 57 minutes

What I would like to do is convert these cells into minutes only.
Maybe set a variable that sets
" day, " or " days, " to 1440
" hour, " or " hours, " to 60
"minute" or "minutes" to 1

and then multiply and put the result in the adjacent cell?

Maybe I'm going about this the entirely wrong way, but any advice would be
appreciated. Thanks.

 

[shockley]

Try this:

Sub Tester()
Dim arrQuant(1 To 3) As Long
Dim arrUnit(1 To 3) As String
arrUnit(1) = "day"
arrUnit(2) = "hour"
arrUnit(3) = "minute"
LastRow = Cells(65536, 1).End(xlUp).Row
For i = 1 To LastRow
sTest = Cells(i, 1)
For j = 1 To 3
UnitPos = InStr(1, sTest, arrUnit(j))
If UnitPos <> 0 Then
arrQuant(j) = Val(sTest)
CommaPos = InStr(1, sTest, ",")
If CommaPos <> 0 Then
sTest = Mid(sTest, CommaPos + 2)
Else: Exit For
End If
End If
Next j
Cells(i, 2) = 24 * 60 * arrQuant(1) _
+ 60 * arrQuant(2) + arrQuant(3)
sTest = ""
Erase arrQuant
Next i
End Sub

HTH,
Shockley