Allowing date-time field to handle more than 24 hours

[Fr@nk]

Hello. I have a field in a table which originally was designed to hold units
of time, so the designers set the field to the date/time type, and an input
mask on the control on the form (00:00 format). However, now, users need to
input values greater than 24:00 and this field won't accept them. The users
need to enter a value such as 28:30 hours (28 and a half hours).

Do I have to change this field to a long integer type? Can I create a
user-defined type allowing these entries? I'm trying to avoid using a long
integer type since I'll have to convert the existing data (any tips on the
best way to do this?) from say, 10:45 to 10.75.

Any help is appreciated. Thanks in advance.


F

 

[Douglas J. Steele]

Actually, you can't convert 10:45 to 10.75 using a long integer: long
integers don't have decimal values.

The recommended approach is to store the times in whatever your time
resolution needs to be: as total seconds, or total minutes. Then, write your
own function to format that to times. Something along the lines of:

Function FormatTimeInSeconds(TimeInSeconds As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
Dim lngSecondsRemaining As Long

lngHours = lngSecondsRemaining \ 3600
lngSecondsRemaining = lngSecondsRemaining - (lngHours * 3600)
lngMinutes = lngSecondsRemaining \ 60
lngSeconds = lngSecondsRemaining - (lngMinutes * 60)

FormatTimeInSeconds = Format$(lngHours, "0") & _
":" & Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function

Another option is to leave them as dates, and reformat them yourself:

Function FormatTimeDurations(TimeValue As Date) As String

FormatTimeDurations = Format$(24 * Int(TimeValue) + Hour(TimeValue),
"00") & _
":" & Format$(Minute(TimeValue), "00")
& ":" & _
Format$(Second(TimeValue), "00")

End Function

 

[Fr@nk]

Actually, you can't convert 10:45 to 10.75 using a long integer: long
integers don't have decimal values.

The recommended approach is to store the times in whatever your time
resolution needs to be: as total seconds, or total minutes. Then, write your
own function to format that to times. Something along the lines of:

Function FormatTimeInSeconds(TimeInSeconds As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
Dim lngSecondsRemaining As Long

lngHours = lngSecondsRemaining \ 3600
lngSecondsRemaining = lngSecondsRemaining - (lngHours * 3600)
lngMinutes = lngSecondsRemaining \ 60
lngSeconds = lngSecondsRemaining - (lngMinutes * 60)

FormatTimeInSeconds = Format$(lngHours, "0") & _
":" & Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function

Another option is to leave them as dates, and reformat them yourself:

Function FormatTimeDurations(TimeValue As Date) As String

FormatTimeDurations = Format$(24 * Int(TimeValue) + Hour(TimeValue),
"00") & _
":" & Format$(Minute(TimeValue), "00")
& ":" & _
Format$(Second(TimeValue), "00")

End Function

Oops. I guess I might've meant Decimal type. Anyway, you're right--I'll just
convert the existing data first to separate hours and minutes, and calculate
them as such. I can just do hours * 60 and add the minutes to that for total
minutes. Then I can slice and dice that without having a max of 24 hours.
Thanks for straightening me out.


F