Convert seconds to m:ss.th

  • Thread starter Thread starter brdfrd
  • Start date Start date
B

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
 
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.
 
(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.
Click to expand...

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
Click to expand...

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
 
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
 
(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.
Click to expand...

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
 
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
 
(e-mail address removed) wrote in @g43g2000cwa.googlegroups.com:
Thanks for following up. I am trying to use the function in a query.
Click to expand...

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.
Click to expand...

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
 
Back
Top