Solved: Convert time

[GrahamEL2007]

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

[SamT]

Format string =

[m].ss

[Paul_Hossler]

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

[GrahamEL2007]

Thanks Sam

Thanks Paul, absolutely brilliant, nice code

all the best

Graham

[Paul_Hossler]

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)

[GrahamEL2007]

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

regards

Graham