Link to home
Create AccountLog in
Avatar of sptech

asked on

handle time value greater than 24 hours

I am computing time when my users travel on business.  During the user travel they are given compensation time.  Normally the users do not earn more than 24 hours on a given business trip.  But it does happen. As an example

on 1/1/2013 user departs and earns 14:00 hours of comptime.
on 1/7/2013 user returns and earns 17 hours of comptime.

I need to store the result (31 hours), but I keep getting errors.  I understand that normally it is not recommended to store calculations, but in this regard I have no choice but to store the calculation.  

Up to 24 hours everything works. anything over 24 hours and the app crashes.  How can I handle total time when it is in excess of 24 hours?  I tried the public function (listed below) that I found on this site but that didn't resolve my issue.

Public Function FormatTime(mytempvar)
   Dim Hours As Long
   Dim Minutes As Long
   If IsNull(mytempvar) Then Exit Function
   Hours = Int(mytempvar / 60)
   Minutes = (mytempvar / 60 - Hours) * 60
   If Minutes = 60 Then
       Hours = Hours + 1
       Minutes = 0
   End If
   FormatTime = Hours & ":" & Format(Minutes, "00")
    CTtotal = FormatTime
End Function
Avatar of Jim P.
Jim P.
Flag of United States of America image

Have you tried storing it as an integer of minutes or a float  and using the dateadd function?

Time is saved in the database as a fraction of 1 where 1 is 1 day (24 hours).  Since you need to store more than 1 hour as time you should store the result as a number then convert to time.

I.e. 36 hours converts to 1.5, etc.

You can get the hours back by multiplying by 24.


Avatar of Patrick Matthews
Extending Bill's example...

I would handle this by storing the date AND time of departure and arrival for the different segments of the trip, and then store the result as either a total number of minutes, or as decimal hours.  For example:

TripID   SegmentID   Depart                Arrive
1        1           2013-02-01 06:00:00   2013-02-01 20:00:00
1        2           2013-02-07 20:30:00   2013-02-08 13:30:00

Open in new window

I would use a query like this to get the time for the trip:

SELECT TripID, Sum([Arrive] - [Depart]) * 24 AS TravelHours
FROM tblTravelSegments

Open in new window

see this link

scroll down the page and look for GetElapsedTime()
Avatar of sptech


decimal hour? could someone please explain this and maybe provide an example?

As above.  A decimal hour is essentially the decimal component of a 24 hour time period.

Try this in the immediate window:
?cdbl(#1/1/2013 1:00AM#)

1 hour = 1/24 = .04166666
Today is 41275 which is computed essentially from the start of 1900.

Hope that helps.



If you save Date() the field actually contains the numerical equivalent of the number year plus ".000000".  If you save Now() the decimal component is saved as well.

Today is 2/10/2013
?Cdbl(Date()) = 41315.000000
?Cdbl(Now()) = 41315.459560 (approx.  11:00 AM today)


Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of sptech


I am not lying when I say that I don't understand everything  Cactus_Data wrote in his function, but it works and that is what I need for now.  Thanks!!!!!
It builds a string of the total hours and minutes.

  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))

Fix(datTime) removes the time part from the value leaving days only.
Days are multiplied by 24 to have an hour count for the days part.
Hour(datTime) returns the hour count from the time part only.
CStr converts the sum of hours to text.

  strMinute = Right("0" & CStr(Minute(datTime)), 2)

Minute(datTime) returns the minute count.
CStr converts the value to text. A zero is prefixed.
Right( ... , 2) picks the last two characters of the minute string, cutting the zero from minute counts beyond 10.

  strHourMinute = strHour & strSeparator & strMinute
This concatenates the parts to the final string.