Convert text/general format time into numeric value that can be filtered

Question

I have time data that is formatted as the following: 000:01:18

The problem is it is formatted as General, which is not recognized by excel as a numerical value at all. So no formulas will convert the data. I want to convert the data into seconds as a simple formatted number. For example the above number will be 78 seconds.

Can someone help me convert this text data into numerical data? Then I need a formula to convert it to seconds.

Thank you!

Answer 1

I suppose you have the data in format hh:mm:ss (instead of hhh:mm:ss as in your example), in this case the formula to convert to time is:
TIME(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2)).
To convert that to seconds you need to multiply it by 24*60*60 (https://support.microsoft.com/en-us/kb/214094?wa=wsignin1.0)

Answer 2

If we start with:

and we want to convert to seconds "in place", then select the cells you wish to convert and run this small macro:

Sub MakeSeconds()
   Dim sec As Long, H As Long, M As Long, S As Long
   Dim r As Range

   For Each r In Selection
      v = r.Text
      If InStr(v, ":") > 1 Then
         ary = Split(v, ":")
         H = CDbl(ary(0)) * 3600
         M = CDbl(ary(1)) * 60
         S = CDbl(ary(2))
         sec = H + M + S
         r.Clear
         r.Value = sec
      End If
   Next r
End Sub

This will result in: