Field to enter ONLY time.

[June7]

However, for some explained reason the OP is entering a date (integer) portion with an integer value of 2.

Did you mean "unexplained"?

 

[GPGeorge]

Spoiler:

And that is as it should be. It is a mistake to use a format at the table level in an attempt to hide the true value of the table column. You could get a huge dent in your head and loose all your hair after days of debugging when you do this to yourself. NEVER format data in a table. PERIOD.

The format does not change the stored value. It merely hides it from you.

Spoiler:

So you will drive yourself crazy trying to figure out why the two values that look like 2:30 are not identical.

The date/time data type is a "point in time" and time includes date. If the user must enter the time you probably won't run in to trouble with data values if you add some proper validation to the form's BeforeUpdate event. But if you use Now() to populate the field, you are in a world of hurt if you ever expect to compare two values because 2:30 PM may in fact not be exactly 2:30 since the date value is a double precision number and it is actually rounded to minutes and seconds for display but does not represent the actual stored value. Given that, I also store time as a string.

I think that's the best summation of the situation I've seen.

 

[DickyP]

The point being covered here by some and ignored by some is that the date/time data type is exactly that and the time element is a date fraction. If all you are interested in is time values then it is the wrong data type!

 

[The_Doc_Man]

I might disagree slightly, DickyP - a date/time variable is the easiest way to fiddle with time - if you just understand that there is a helper AND a barrier to force your hand. The helper is that Office manipulates time pretty much uniformly across all its platforms. The reference point might change (1/1/1900 or 12/30/1899 or other reference points as they occur) but they are TIMELINES - a number representing an elapsed time excursion from start time to finish time.

This makes time easy in one way, because #10:45# is an unequivocal time - as is #12:34:56# or #00:00:00#. But the problem actually lies with the date/time FORMAT routines. THEY are the source of the problem. They will not work with fractions of a second, and if you accumulate more than 24 hours of time, there is no format such as "hhhh:mm" (long number of hours, normal minutes). It is the formatting that complicates time.

 

[The_Doc_Man]

That is because the date/time data type is a "point in time". It is useful for entering when something did or will happen. But if you want to calculate elapsed time, you need to convert to straight minutes or seconds depending on the precision you want in the result.

Agree - which is why "roll your own time function" is a common answer to people with time-display problems.

 

[Mark_]

@ Jen-Jen,
You are asking to enter only time. Is this because you really want to track how much time has elapsed? If so, remember that using a DATE won't let you show a time value of greater than 24 hours. Instead, as the time portion is the decimal, you get your time to wrap. If you don't keep this in mind, you will drive yourself batty trying to figure out why 12 hours plus 14 hours give you 2 hours. So long as you remember this you can look at the integer value to give you days.

 

[Ken Sheridan]

............remember that using a DATE won't let you show a time value of greater than 24 hours.

That's only a limitation of the built in formatting of values of DateTime data type. The underlying value is merely a 64 bit floating point number, so lends itself very easily to doing time arithmetic, and the result can easily be formatted to whatever you wish, e.g.

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String
           
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
   
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
   
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
   
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
   
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
   
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
            Format(dtmTime, ":nn:ss")
    End If
   
End Function

? TimeDuration(#2025-07-01 18:30:56#,#2025-07-31 14:20:21#)
715:49:25

Or, if values are entered as time only, and allowing for 'shift' times spanning midnight:

? TimeDuration(#18:30:56#,#14:20:21#)
19:49:25

For entering values of 24 hours or more this can be done as a string which is then parsed to return a value of DateTime data type on which arithmetical operations can be performed:

Public Function TimeText2DateTime(varTime As Variant) As Variant

    Dim intHours As Integer
    Dim intMinutes As Integer
    Dim intSeconds As Integer
   
   
    If IsNull(varTime) Then
        TimeText2DateTime = Null
    Else
        ' parse string into hours,minutes, and seconds values
        intHours = Left(varTime, InStr(varTime, ":") - 1)
        intMinutes = Mid(varTime, InStr(varTime, ":") + 1, 2)
        ' if seconds have been entered get seconds value
        ' otherwise seconds = zero
        If InStr(varTime, ":") <> InStrRev(varTime, ":") Then
            intSeconds = Mid(varTime, InStrRev(varTime, ":") + 1)
        Else
            intSeconds = 0
        End If
       
        ' compute date/time value from hours, minutes, and seconds values
        TimeText2DateTime = (intHours + intMinutes / 60 + intSeconds / 3600) / 24
    End If
       
End Function

? TimeText2DateTime("48:30:56")+TimeText2DateTime("24:20:21")
3.03561342592593

which can then be formatted using the following function:

Public Function TimeElapsed(ByVal dtmTime As Date, strMinSec As String, _
            Optional ByVal blnShowdays As Boolean = False) As String

    ' Returns a date/time value as a duration
    ' in hours etc or days:hours etc if optional
    ' blnShowDays argument is True.
    ' Time format is determined by strMinSec argument,
    ' e.g. "nn" to show hours:minutes,
    ' "nn:ss" to show hours:minutes:seconds,
    ' "" to show hours only
    ' Call the fucntion, in a query for example, like this:
    ' SELECT EmployeeID,
    ' TimeElapsed(SUM(TimeDurationAsDate(TimeStart, TimeEnd)), "nn") As TotalTime
    ' FROM TimeLog
    ' GROUP BY EmployeeID;
   
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    Dim IsNegative As Boolean
   
    ' if duration is a negative value then flag as IsNegative
    ' and convert to a positive value
    If dtmTime < 0 Then
        IsNegative = True
        dtmTime = Abs(dtmTime)
    End If
   
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
   
    If blnShowdays Then
        TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":" & strMinSec)
    Else
        TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
            Format(dtmTime, ":" & strMinSec)
    End If
   
    ' remove trailing colon if necessary
    If Right(TimeElapsed, 1) = ":" Then
        TimeElapsed = Left(TimeElapsed, Len(TimeElapsed) - 1)
    End If
   
    ' add minus sign if duration is a negatve value
    If IsNegative Then
        TimeElapsed = "-" & TimeElapsed
    End If
   
End Function

? TimeElapsed(3.03561342592593,"nn:ss")
72:51:17

 

[Mark_]

@Ken, thank you for showing that that OP will need to convert to a different data type (string) for display!