Conveting HH:MM:SS into Mins

[gbabu]

Hello All,

<table style="width: 511px; height: 151px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:4608;width:95pt" width="126"> <col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> <tbody><tr style="mso-height-source:userset;height:37.5pt" height="50"> <td class="xl64" style="height:37.5pt;width:95pt" height="50" width="126">Ticket
Start Time</td> <td class="xl63" style="border-left:none;width:81pt" width="108">Ticket
End Time</td> <td class="xl63" style="border-left:none;width:97pt" width="129">MTTR</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">13/08/2011 10:26</td> <td class="xl66" style="border-top:none">09/08/2011 03:38</td> <td class="xl67" style="border-top:none">27:20:25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">08/08/2011 08:24:00</td> <td class="xl66" style="border-top:none;border-left:none">08/08/2011 09:15</td> <td class="xl67" style="border-top:none;border-left:none">149:22:36</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">08/08/2011 09:01:00</td> <td class="xl66" style="border-top:none;border-left:none">08/08/2011 11:51</td> <td class="xl67" style="border-top:none;border-left:none">148:45:36</td> </tr> </tbody></table>I have written few code for converting hh:mm:ss into mins, but its not happening.

For i = 2 To 20

Range("K" & i).Select
cycle = ActiveCell.Value
HourTime = "=Hour(" & cycle & ")*60"
HourTime = HourTime + "+(Minute(" & cycle & "))"
HourTime = HourTime + "+(Second(" & cycle & "))/ (60)"
ActiveCell.FormulaR1C1 = HourTime
Next i

Once i run the code, I'm getting wrong answer. Please help me.

 

[DonkeyOte]

If you want to convert 27:20:25 to 1640.42 mins then multiply the time value by 1440 and format resulting value to 2 decimal places (format as number rather than Time)

 

[gbabu]

If you want to convert 27:20:25 to 1640.42 mins then multiply the time value by 1440 and format resulting value to 2 decimal places (format as number rather than Time)

Could you please expain exactly what did you say? i didn't understand

 

[DonkeyOte]

In XL Time is effectively decimal: 1 hour = 1/24 (so 12 hours = 0.5)

If you want to show a time value in a given unit (hours, minutes, seconds) where the integer reflects the unit then we multiply the time value appropriate to the unit... for hours this would be 24, for minutes 1440 (24*60) and for seconds 86400 (24*60*60)

In code terms...

Sub Example()
    Dim rngCell As Range
    For Each rngCell In Range("K2:K20").Cells
        With rngCell
            If Val(.Value) Then
                .Value = .Value * 1440
                .NumberFormat = "0.00"
            End If
        End With
    Next rngCell
End Sub

or, using Evaluate route:

Sub Example2()
    With Range("K2:K20")
        .NumberFormat = "0.00"
        .Value = Evaluate("IF(ISNUMBER(" & .Address & ")," & .Address & "*1440,REPT(" & .Address & ",1))")
    End With
End Sub

The former is by far the more practical of the two methods (the latter is more for illustrative purposes)

 

[gbabu]

Thank you so much. Its code is working fine.

In XL Time is effectively decimal: 1 hour = 1/24 (so 12 hours = 0.5)

If you want to show a time value in a given unit (hours, minutes, seconds) where the integer reflects the unit then we multiply the time value appropriate to the unit... for hours this would be 24, for minutes 1440 (24*60) and for seconds 86400 (24*60*60)

In code terms...

Sub Example()
    Dim rngCell As Range
    For Each rngCell In Range("K2:K20").Cells
        With rngCell
            If Val(.Value) Then
                .Value = .Value * 1440
                .NumberFormat = "0.00"
            End If
        End With
    Next rngCell
End Sub

or, using Evaluate route:

Sub Example2()
    With Range("K2:K20")
        .NumberFormat = "0.00"
        .Value = Evaluate("IF(ISNUMBER(" & .Address & ")," & .Address & "*1440,REPT(" & .Address & ",1))")
    End With
End Sub

The former is by far the more practical of the two methods (the latter is more for illustrative purposes)