Solved: Convert time

Hi all

I have cells formmatted as General and times in minutes and seconds displayed like 6m 42.50s

How can I display them like this 402.50

Thanks in advance

Format string =

Quote:

---

[m].ss

---

I think that the 6m 42.50s is a string, so you'll probably need to do more than apply different cell formatting

You could use a User Defined Function to seperate the minutes part of the string from the seconds and convert to a double

You'd have to apply Number+2Decimal formating to the cells tho since a UDF can't change a worksheet like that

[vba]

'6m 42.50s = 60*60 + 42.5 = 402.50
' to show the last zero, format the cell as Number with 2 Decimals

Function ReformatTimes(s As String) As Variant
Dim s1 As String
Dim i As Long
Dim d As Double

ReformatTimes = CVErr(xlErrNA)
On Error GoTo NiceExit

i = InStr(1, s, "M", vbTextCompare)

If i > 0 Then
d = 60# * CDbl(Left(s, i - 1))
s1 = Right(s, Len(s) - i)
Else
d = 0#
s1 = s
End If

s1 = Left(s1, Len(s1) - 1)

ReformatTimes = d + CDbl(s1)

Exit Function
NiceExit:
End Function
[/vba]

Paul

Thanks Sam

Thanks Paul, absolutely brilliant, nice code

all the best

Graham

Graham -- hope it works for you

It could use more error checking, and you could expand it to handle hours also.

I had it return a number so that it could used in a calculation, but you could have it return a Date (which also includes the Time) so that you could apply Excel DateTime formating, or even a String with the formating just the way you'd like it.

Personally, I'd leave it as a number, but you should be able to mod it to meet your needs.

Paul

(PS. You can use Thread Tools above your first post to make this thread as Solved)

I'll leave it as a number for now, thanks Paul

regards

Graham