Convert Time Format to Seconds

[paulfitz320]

Hi,
I have a column of minutes and seconds in COL A in the following format - 1m 34.47s - Would anyone have a formula or method that can convert these to seconds only in COL B please?

 

[Artik]

In standard module eg. Module1 paste the code:

Function Seconds(ByVal vInput As String) As Double
    Dim dblMins As Double
    Dim dblSecs As Double

    vInput = LCase(vInput)

    If Len(vInput) = 0 Then
        Seconds = 0
    ElseIf InStr(1, vInput, "m", vbTextCompare) > 0 Then
        dblMins = CDbl(Split(vInput, "m")(0))
        dblSecs = CDbl(Val(Split(vInput, "m")(1)))
        Seconds = dblMins * 60 + dblSecs
    ElseIf InStr(1, vInput, "m", vbTextCompare) = 0 Then
        Seconds = CDbl(Val(vInput))
    End If
End Function

In B1 type formula:

=Seconds(A1)

Artik

 

[rollis13]

Hi to all.
Supposing you are starting from cell A1 use this formula in cell B1:
=LEFT(A1,FIND("m",A1)-1)*60+MID(A1,FIND("m",A1)+2,LEN(A1)-FIND("m",A1)-2)

 

[paulfitz320]

Both worked. Will tick rollis13 as the solution.
Thanks to you both. Much appreciated.

 

[rollis13]

Thanks for the positive feedback, glad we were able to help.

 

[Artik]

My fingers hurt from typing that formula. It is definitely shorter to write =Seconds(A1).
But I'm on another matter. In Poland, this formula will not work because of the decimal separator (in Poland - comma). It would be necessary to expand the formula by replacing the dot with a comma. Leaving this problem aside, with all due respect to rollis13's knowledge, the formula does not stipulate that the string can contain the term only minutes or seconds and redundant spaces. The macro handles these cases perfectly.

Artik

 

[Dave Patton]

Time 2023.xlsm
	A	B	C
1	1m 34.47s	94.47	94.47
2
3b

Cell Formulas
Range	Formula
B1	B1	=LET(s,FIND("m",A1)+2,LEFT(A1,FIND("m",A1)-1)*60+MID(A1,s,LEN(A1)-1-s))
C1	C1	=TEXTBEFORE(A1,"m")*60+TEXTAFTER(TEXTBEFORE(A1,"s"),"m ")