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

 

[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 )