Convert seconds to m:ss.th

[brdfrd]

I have read the posts that I can find on this subject but I still have
trouble undertanding how to implement a conversion from seconds i.e.
234.45 to m:ss.00. I am actually after a way to change things based on
the numbers as well because I will use the results in Data Pages at a
swim meet. So we have races where times range from under a minute to
just over. We also have races in the 19 minute range. Most of them fall
between 20 seconds and 20 minutes so I would love a solution to handle
all of them. I have tried some of the solutions but can't get them to
work in all cases.

Thanks for any help,
Brad

 

[Vincent Johns]

Maybe this will help. I tried converting them as Date/Time values, but
I kept losing the fractional seconds, so I gave up on that.

[SwimTimes] Table Datasheet View:

Time
-------
234.45
1175.38
20.01

This Query should give you the kinds of values you want to see.

[Q_Times] SQL:

SELECT SwimTimes.Time,
[SwimTimes]![Time]\60 AS Minutes,
Format([Minutes],"00") & ":"
& Format([SwimTimes]![Time]-[Minutes]*60,"00.00")
AS MinsCalc
FROM SwimTimes
ORDER BY SwimTimes.Time;

[Q_Times] Query Datasheet View:

Time Minutes MinsCalc
------- ------- ---------
20.01 0 00:20.01
234.45 3 03:54.45
1175.38 19 19:35.38

You could revise the format to omit the leading zeroes, if you wish.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

(e-mail address removed) wrote in @g49g2000cwa.googlegroups.com:

trouble undertanding how to implement a conversion from seconds i.e.
234.45 to m:ss.00.

Set the ControlSource of

Public Function FormattedTime(NumberOfSeconds As Variant) As String

If IsNull(NumberOfSeconds) Then
' empty value
FormattedTime = "(none)"

ElseIf Not IsNumeric(NumberOfSeconds) Then
' nonsense value
FormattedTime = "**ERROR**"

Else
FormattedTime = Format(NumberOfSeconds \ 60, "00\:") & _
Format(NumberOfSeconds Mod 60, "00\.") & _
Format((100 * NumberOfSeconds) Mod 100, "00")

End If
End Function

I am actually after a way to change things based on
the numbers as well because I will use the results in Data Pages

I haven't really bothered with data pages; but if you can't call a
function from within the ControlSource of the text box, then you should
be able to get the same effect by doing the conversion in the SQL of the
query. The exact syntax depends on the back end database you are using,
though, but it's not too hard with a bit of simple arithmetic.

Hope that helps


Tim F

 

[brdfrd]

Tim,

This is great. I am getting a rounding effect when the hundredths are
over.5 . Can I control this in the function or is this something that
needs to be changed in the query or controls in a form or datapage.

Thanks so much for your help so far.

Brad

 

[Tim Ferguson]

(e-mail address removed) wrote in @g43g2000cwa.googlegroups.com:

I am getting a rounding effect when the hundredths are
over.5 . Can I control this in the function or is this something that
needs to be changed in the query or controls in a form or datapage.

Not quite sure what you mean, or what you want: you can make suitable
changes to the last segment of the actual formatting:

FormattedTime = Format(NumberOfSeconds \ 60, "00\:") & _
Format(NumberOfSeconds Mod 60, "00\.") & _
Format((100 * NumberOfSeconds) Mod 100, "00")


to something like

Format(Int((100 * NumberOfSeconds)) Mod 100, "00")

if you want the value truncated rather than rounded. For more
information, look up help on Int, Fix, and Round.

Hope that helps


Tim F

 

[brdfrd]

Tim,

Thanks for following up. I am trying to use the function in a query.
Below are the two fields I have. Fin_Time is being pulled from another
database and I am trying to convert it in field Time to m:ss.th before
putting it through to a DataPage. You can see that any number with
tenths and hundredths over .5 moves the seconds up one. I am just not
sure where to do something differently.

Fin_Time Time
256.30 04:16.30
259.54 04:20.54
264.36 04:24.36
269.60 04:30.60
274.70 04:35.70
277.85 04:38.85
279.32 04:39.32
290.72 04:51.72

Thanks for any help,
Brad

 

[Tim Ferguson]

(e-mail address removed) wrote in @g43g2000cwa.googlegroups.com:

Thanks for following up. I am trying to use the function in a query.

I really don't have enough experience with datapages to know whether you
can use a function like this; in Access proper, I'd put the controlsource
of the text box on the form to something like
=FormattedTime([DurationInSeconds])

so perhaps you can do the same in the dap. Meanwhile, you can also do the
conversion in the SQL, although it's messy and depends on what back end
database you are using (mdb, sql server, oracle etc). You may be better
off asking in a DAP group rather than this one.

You can see that any number with
tenths and hundredths over .5 moves the seconds up one. I am just not
sure where to do something differently.

Frankly it's up to you: you could (a) truncate to the two digits (i.e.
rounding down), or (b) round to the nearest hundredth, or (c) round to
the nearest even number of hundredths (something Microsoft thought up
apparently), or (d) just carry on displaying as many digits as you like.
It should have been specified in the systems requirements document that
you wrote up before starting to code the project...... <g>

All the best


Tim F

 

[Bob Miller]

If you are going to be doing this often then perhaps a function would be
the answer.
I created Tim(DecMin) where DecMin is the time in decimal minutes you
want to convert. You can probably add to this to get tenths of a
second.
Here is the function:
Function Tim(DecTime As Single) As String
Dim hours, minutes, seconds As Integer
Dim Sec$, Min$
hours = Int(Val(DecTime) / 60)
minutes = Int(Val(DecTime) - (hours * 60))
minutes = Str(minutes)
hours = Str(hours)
seconds = Str((Val(DecTime) - minutes) * 60)
If minutes 0 Then
If minutes 10 Then
Min$ = ":" + Right(Str(Int(minutes)), 2)
Else
Min$ = ":0" + Right(Str(Int(minutes)), 1)
End If
Else
Min$ = ":00"
End If
If seconds 0 Then
If seconds 10 Then
Sec$ = ":" + Right(Str(Int(seconds)), 2)
Else
Sec$ = ":0" + Right(Str(Int(seconds)), 1)
End If
Else
Sec$ = ":00"
End If
Tim = hours + Min$ + Sec$
End Function

It probably could be cleaned up but I did it in a hurry and it works
for me.