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

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

GrahamEL2007
04-11-2013, 01:57 AM
I'll leave it as a number for now, thanks Paul

regards

Graham