Convert Minutes and seconds to Hours Minutes and seconds??

[zrazzaq]

Hi:
I have a report that has:
488:30 which is 488 Minutes and 30 seconds
Now I need to convert this to hours and minutes and seconds.
I know to divide the 488/60 but how do I split the string apart to have the seconds in there as well and to show the appropiate format that the users want.
If the value reads 1:39 which is 1 minute and 39 seconds that is ok but anything over 60 minutes they need it in hours.
Anyone got any suggestions?
Thanks
Zishan

 

[Golom]

Try

IIF(Val(Left([MS],Instr(1,[MS],":")-1)) > 60, 
    Val(Left([MS],Instr(1,[MS],":")-1))\60 & ":" & 
    Val(Left([MS],Instr(1,[MS],":")-1)) Mod 60 & 
    Mid([MS],Instr(1,[MS],":")), [MS] )

Where [MS] is the name of your current "Minutes:Seconds" field.

 

[zrazzaq]

I actually needed in VB but it still did not give me the result I needed thanks though
Zishan

 

[Golom]

Uhhh ... that is VB. Just insert the name of a VB variable in place of [MS]. I tested it with

x = "452:27"
? IIF(Val(Left(x,Instr(1,x,":")-1)) > 60, Val(Left(x,Instr(1,x,":")-1))\60 & ":" & Val(Left(x,Instr(1,x,":")-1)) Mod 60 & Mid(x,Instr(1,x,":")), x )

and it produced

7:32:27

Is that what you were after?

 

[PHV]

Another way, in Access VBA:
strMinutes="488:30"
? TimeSerial(0, 0, Eval(Replace(strMinutes, ":", "*60+")))
08:08:30

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zrazzaq]

Thank to all but I wrote this:

Public Function ConvTime(sSecc As String)

   Dim iPoss As Integer
    'iPos = InStr(n, ":")
    p = Len(sSecc)
    q = 3
    'Lets get seconds first
    strn = Right$(sSecc, q)
    
    'To get minutes
    n = Left$(sSecc, p - q)
    If n < 60 Then
        n = sSecc
        ConvTime = n
    Else
        n = FormatNumber(Div0(n, 60), 2)
        n = ReplaceCharacter(n, ".", ":")
        ConvTime = n & strn
    End If
End Function

Cool
Thanks
Zishan

 

[PHV]

And what is Div0 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

The follow up is here: thread705-1252830