PDA

View Full Version : Solved: Convert time



GrahamEL2007
04-09-2013, 11:26 AM
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
04-09-2013, 12:33 PM
Format string =
[m].ss

Paul_Hossler
04-09-2013, 07:03 PM
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



'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


Paul

GrahamEL2007
04-10-2013, 07:41 AM
Thanks Sam

Thanks Paul, absolutely brilliant, nice code

all the best

Graham

Paul_Hossler
04-10-2013, 02:47 PM
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
04-11-2013, 01:57 AM
I'll leave it as a number for now, thanks Paul

regards

Graham