help with excel formula for time conversion

[Grenache]

Hello,

I have a spreadsheet with a column containing time (duration) in the format below:

1m 3s
10m 12s
4m 30s
11m 8s

Each time is in text format in a single cell, and I would like to have a formula that I can use to convert these text cells into a time value, which excel can then sum up to calculate the total minutes and seconds in the column.

I looked at the list of available formulae but I can't seem to find one. Would I have to work out where the "m" and "s" were in the string, divide the string accordingly and use Value and Mid to break it up then add the minutes & seconds to make a number?

Thanks

G

 

[Sin_Chase]

This looks like a bit of coding rather than a straight up formula.

String split on the space discarding the space. Discard the m and s. Convert all to seconds, add them up, dump them where you want them on spreadsheet.

Pretty easy coding but one of the formula wizards might know a formula way.

 

[Grenache]

aha, thanks for the tip, it helped me work it out. The formula is as follows. It assumes that cell A1 contains the text string, and when I copy the formula, the reference to A1 changes accordingly....

I have split it on to separate lines for clarity, and to explain it.

=(
VALUE(LEFT(A1,(FIND("m",C8,1)-1))) - gets the minutes value before the m
/ 1440 - converts it to a fraction of a day
)
+(
VALUE(LEFT(RIGHT(A1,3),2)) - gets the seconds value,
from the first two characters of the last three characters
/ 86400 - converts it to fractions of a day
)

and I then format the cell to hh:mm:ss and it displays ok, and I can then sum up the column to get the total duration.

So in excel, it looks like this:

=( VALUE(LEFT(A1,(FIND("m",C8,1)-1))) / 1440) + ( VALUE(LEFT(RIGHT(A1,3),2)) / 86400 )

 

[james.miller]

VBA:

Option Explicit
Function TextToTime(str As String)

Dim iMins As String
Dim iSecs As String

Dim midpoint As Integer
Dim timestring As String

' Find the middle of the string
midpoint = InStr(1, str, " ")

' Split out the minutes
iMins = Left(str, midpoint - 2)

' Split out the seconds
iSecs = Trim(Right(str, Len(str) - midpoint))
iSecs = Left(iSecs, Len(iSecs) - 1)

' build a string using the format hh:mm:ss
timestring = Int(iMins / 60) & ":" & (iMins Mod 60) + (Int(iSecs / 60)) & ":" & iSecs Mod 60

' Convert timestring to a decimal that excel can work work
TextToTime = CDate(timestring)

End Function

put that in a vba module and then this in a cell

=TextToTime(A1)

Format that cell as a Time and that's it. Nice and neat with no sheet formulas involved