Adding up time

[reissad]

What settings do I have to have in my table data type etc to be able to add up times. I want to be able to put these amounts in a report and total them. ie 40minutes + 40 minutes = 1hr 20min

 

[The_Doc_Man]

You want a date/time variable.

The problem you will have is that a time variable is rather ... ugly when you get down to using it for anything but date/time.

A date/time variable is actually a double - I.e. 64-bit floating - number. The integer part is days since the system reference date. The fraction part is the fraction of a day since midnight.

As long as your differences are small, you can treat date/time variables in a more or less straightforward way. For example, doing this in VBA,

dtAccumulated = 0.0

will reset the date variable. You really want the decimal point.

dtAccumulated = dtAccumulated + ( dtLaterTime - dtEarlierTime )

where the dt prefix is what I use to show you it is a date/time variable.

The trick is extracting the stuff you want from dtAccumulated when you are done. Date variables carry that baggage about the system reference date. So if your times exceed 24 hours you will have some "stuff" to deal with.

You might try as an experiment the idea of having two parts to this operation. Look up the FORMAT function, then click on the help link "See Also." From that list, find the User-Defined Date/Time Formats help. I'm betting that to get the format you want for the time portion, you'll have to use

Format( dtAccumulated, "hh:nn:ss" )

Convert the date to a true double using CDbl function. If the absolute value of the result is greater than 1, truncate it using either INT or FIX (q.v. in the help files to decide which one you want to use.) Then use a long or integer variable to hold the result of the INT or FIX operation. You can then format the integer any way that integers are normally formatted.

If you are doing this in queries, it should work OK BUT there is a warning to be added. If you do this sort of stuff with a SELECT query that you can open in "datasheet" view, the accumulated dates might look funny if they aren't formatted correctly as I described earlier.

 

[DJN]

I had a similar problem, in that I needed to hours and minutes not the time difference. In other words 30 minutes and 1 hour 45 minutes which should be 2 hours 15 minutes. I created a function that converted the hours to minutes, added them together and then output to hours and minutes. May be a bit clunky but it works.

Function ConvertToMinutes(Convert)

Dim Hours
Dim Minutes
Dim MinutesInHours
Dim TotalMinutes
Dim MyNumber



'Convert to whole number
Convert = Convert * 100

' Convert to a string, trimming extra spaces.
MyNumber = Trim(str(Convert))

'check that time is valid
If MyNumber = "0" Then
Exit Function
End If

'Find number of hours
Hours = Val(Left(MyNumber, Len(MyNumber) - 2))

'Convert to minutes
MinutesInHours = Hours * 60

'Find number of minutes
Minutes = Val(Right(MyNumber, 2))

'Add together
TotalMinutes = (MinutesInHours + Minutes)

'return result
ConvertToMinutes = TotalMinutes

End Function

Hopes this helps.


David

 

[raskew]

The following two functions may be of assistance or at least provide a starting point.

Function timediff(starttime As Date, endtime As Date) As Variant
'*******************************************
'Name:      timediff (Function)
'Purpose:   Return the number of minutes between
'           two times, dates or date/times
'           Calls Timesay() to convert minutes
'           to a string
'Inputs:    (1) ? timediff(#12:31#, #19:10#)
'           (2) ? timediff(#07/04/02#, #07/06/02#)
'           (3) ? timediff(#07/04/02# + #12:31#, #07/06/02# + #19:10#)
'Output:    (1) 0 days 6 hours 39 minutes
'           (2) 2 days 0 hours 0 minutes
'           (3) 2 days 6 hours 39 minutes
'*******************************************

Dim intHold As Long

timediff = timesay(DateDiff("n", starttime, endtime))

End Function

'*******************************************

Function timesay(pInt As Long) As String
'*******************************************
'Name:      timesay (Function)
'Purpose:   Converts long integer representing
'           number of minutes to a day/hour/minutes
'           string
'Inputs:    ? timesay(795)
'Output:    0 days 13 hours 15 minutes
'*******************************************

Dim intHold As Long
Dim strTime As String

intHold = pInt

strTime = intHold \ 1440 & " days "
intHold = intHold - ((intHold \ 1440) * 1440)
strTime = strTime & intHold \ 60 & " hours "
intHold = intHold - ((intHold \ 60) * 60)
strTime = strTime & intHold Mod 60 & " minutes "
timesay = strTime
End Function