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

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

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

Hi,

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.

Regards,

Bill

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.

Regards,

Bill

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:

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

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
```

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

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

see this link

http://support.microsoft.com/?kbid=210604

scroll down the page and look for GetElapsedTime()

http://support.microsoft.com/?kbid=210604

scroll down the page and look for GetElapsedTime()

ASKER

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

Hi,

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.

Regards,

Bill

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.

Regards,

Bill

Further...

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)

Regards,

Bill

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)

Regards,

Bill

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

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.

/gustav

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.

/gustav