 # Does Access support a "Time" format (not "Time Of Day")?

Does Access support a Time format, as in MM:SS.TT, as in, for example, 15:10.42, meaning 15 minutes, 10.42 seconds)?  Or am I relegated to this being a text field?

If you're keeping track and doing stats of timed athletic performance, swimming and running being prime examples, it would sure be nice to be able to do mathematical functions using this as a format.

2 Replies

# Re: Does Access support a "Time" format (not "Time Of Day")?

Let's be clear about what you want. You correctly call this out as 'not "Time of Day"'. And you also correctly identify it as a "Time" format, i.e. not a time value.

What you want is an elapsed time, which is a number of minutes, or number of seconds, etc., FORMATTED in such as way as to resemble a standard time. So we can apply formatting to many values, numbers as well as strings, to DISPLAY them in whatever format we need.

So, the answer is yes, to display this as an elapsed time, format a text string that way. It's not a time of day anyway.

That said, if you want to do math calculations on dates and times, you have to use the actual dates and times. The DateDiff() formula will do all this for you.

The way to do it is to calculate the total number of Seconds, or tenths of seconds between the two dates and then further calculate the hours, minutes, seconds, and tenths needed from that total elapsed time. Then concatenate those pieces as you want.

# Re: Does Access support a "Time" format (not "Time Of Day")?

Access does not support this by default. You will need custom coding for this.

To read and convert such a string to a date/time value including the hundredths of seconds:

``````' Converts a string expression for a sport result with
' 1/100 of seconds or 1/1000 of seconds to a date value
' including milliseconds.
'
' Example:
'   "3:12:23.48"    ->  03:12:23.480
'     "20:06.80"    ->  00:20:06.800
'        "19.56"    ->  00:00:19.560
'       "49.120"    ->  00:00:49.120
'       "23.328"    ->  00:00:23.328
'
' 2018-01-24. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function CDateRaceTime( _
ByVal RaceTime As String) _
As Date

Dim Values      As Variant
Dim Hour        As Integer
Dim Minute      As Integer
Dim Second      As Integer
Dim Millisecond As Integer
Dim Result      As Date

Values = Split(RaceTime, MillisecondSeparator)
Select Case UBound(Values)
Case 0
' No split seconds.
Case 1
Millisecond = Val(Left(Values(1) & "00", 3))
Case Else
' Invalid expression.
End Select

If UBound(Values) <= 1 Then
' Split time part.
Values = Split(Values(0), TimeSeparator)
Select Case UBound(Values)
Case 0
Second = Val(Values(0))
Case 1
Second = Val(Values(1))
Minute = Val(Values(0))
Case 2
Second = Val(Values(2))
Minute = Val(Values(1))
Hour = Val(Values(0))
Case Else
' Invalid expression.
Millisecond = 0
End Select
Result = TimeSerialMsec(Hour, Minute, Second, Millisecond)
End If

CDateRaceTime = Result

End Function``````

and to format those values for display:

``````' Formats a time duration rounded to 1/100 second with trailing zeroes
' and with no leading hours and minutes if these are zero.
' This format is typical for sports results.
'
' Examples:
'   1:02:07.803 ->  1:02:07.80
'     02:07.803 ->     2:07.80
'        14.216 ->       14.22
'
' 2016-12-11. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatRaceTime( _
ByVal Expression As Variant) _
As String

Const FormatHour    As String = "h:n"
Const FormatMinute  As String = "n:s"
Const FormatBase    As String = "s.ff"

Dim Duration    As Date
Dim Format      As String
Dim Result      As String

If IsDateMsec(Expression) Then
Duration = CDateMsec(Expression)
If Hour(Duration) > 0 Then
Format = FormatHour & FormatMinute
ElseIf Minute(Duration) > 0 Then
Format = FormatMinute
End If
Format = Format & FormatBase
Result = FormatExt(Expression, Format)
End If

FormatRaceTime = Result

End Function``````

Both functions use several supporting functions - too much to post here.

For the full code, look up my project VBA.Date

The two functions above are taken from module DateText