Displaying Seconds As Minutes:Seconds.

[Spyder1000]

I’ve got a list of times in seconds. Now I know I can just device it by 60 to convert it to Minutes and the Decimal version of seconds. But I’d rather be able to display the time in Minutes:Seconds. Any suggestions?

 

[dhookom]

Check out Doug Steele's page

http://members.rogers.com/douglas.j.steele/Diff2Dates.html.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[formerTexan]

Good morning,

The following will work, but keep an eye on your data types. The same approach can be used if a further conversion to hours is needed. I can't help but think there is an existing VBA function for this, but never found it the last time I looked.

Dim iSec As Integer
Dim iMin As Integer
Dim iM As Integer
Dim strT As String
iSec = YourValueInSeconds
iMin = Round(iSec / 60)
iM = iSec - (iMin * 60)
strT = CStr(iMin) & ":" & CStr(iM)

 

[BobStubbs]

Here is a way to achieve your result, using integer division and modulus arithmetic. I think these are useful, but I don't often see them used. Start with e.g. 123 seconds. Try this in the Immediate window, to see the results:

Debug.print 123 / 60
2.05
Debug.print 123 \ 60
2
Debug.print 123 mod 60
3

The first line is 'normal' division.
The second is integer division - the result is rounded down to show the integer part of the answer
The third is the remainder when (in this case) 123 is divided by 60.

You can use this to convert an integer number of seconds, to minutes and seconds. Here is a sample procedure which you can paste into a module, to test the idea:

Sub TimeFormattingTest()

Dim intTotalSeconds As Integer
Dim intSeconds As Integer
Dim intMinutes As Integer
Dim strMyTime As String

intTotalSeconds = 123
intSeconds = intTotalSeconds Mod 60
intMinutes = intTotalSeconds \ 60
strMyTime = Format$(intMinutes, "00") & ":" & Format$(intSeconds, "00")
MsgBox strMyTime

End Sub

In my example, strMyTime would contain the value 02:03

I hope that this helps.


Bob Stubbs