Summing columns of time

[Guest]

Hi,
In my report, I want to sum the time for a particular item. I have a field,
Duration, and it can have anywhere from 5 to 19 lines of time (short time).
Nothing I try seems to work.
Thanks

 

[Rick Brandt]

Hi,
In my report, I want to sum the time for a particular item. I have a
field, Duration, and it can have anywhere from 5 to 19 lines of time
(short time). Nothing I try seems to work.
Thanks

What is the data type? The DateTime data type is for points in time, not
for durations. It might initially appear to work for durations, but that
breaks down when you exceed 24 hours in which case the day advances and the
time wraps around.

Durations should be stored as a numeric type (number of seconds or number of
minutes, etc.).

 

[dannis]

May be so:
add field "duration_in_minutes" (seconds, milisec etc) to your report's data
source
then sum it in particular field as one of totals of the section. Write a
function to convert that sum to "HH:MM" format
or to what you need.
You also can make these additional fields invisible.

Denis.

 

[Guest]

As Rick has pointed out the date/time data type is not intended for storing
time duration values. It is in fact a 64 bit floating number under the skin
and implements date/time values as an offset from 30 December 1899 00:00:00.
When you insert a time you are in fact inserting a number of less than 1
corresponding to a point of time on that date.

If you have used a date/time data type column in your table you can sum the
values, but where the sum exceeds 23:59:59 you'll get a date/time value on 31
December 1899 or later, which if formatted as a time will just show the time
of day on that date. You could return it as a time duration value as a
string, however, using the following function, which I've amended slightly
from one which returns the duration between two date/time values:

Public Function TimeDuration( _
dblDuration As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function

Paste the above into a standard module in your database and in your report
call it in an expression used as the ControlSource of a text box in a group
or report footer:

=TimeDuration(Sum([Duration]))

If you want to show values of 24 hours or more as # day(s) hh:nn:ss rather
than as hh:nn:ss (with the hours showing as 24, 25 etc) then pass True as the
second optional argument:

=TimeDuration(Sum([Duration]),True)

If you should ever need to insert an individual duration of 24 hours or more
into your table there would be no way you could so with a date/time data type
of course. A solution in such cases would be to have separate columns in the
table for hours, minutes and seconds and combine the values (hours*3600 +
minutes*60 + seconds) to give a total in seconds which you can use for
computations, then format the result back to a hh:nn:ss format by means of a
function which makes use of integer division and the Mod operator.

Ken Sheridan
Stafford, England

 

[dale earnhardtjr]

hi are you good luck in your future

 

[Guest]

Ken,

Whew, thats a lot to chew on as a rookie. I couldn't get it to work. I
went to MS Visual Basic, pasted your code, and named the module "Duration."
The top line of the module has "Option Compare Database" (Declarations) and
when I click on your script, it shows as "TimeDuration."

I made a new text box with your "=TimeDuration(Sum([Duration])) " in the
Control Source. If I put in the "Detail" i get a Data type mismatch in
criteria expression. If I put it in the page footer, I don't want page
totals, I want individual sums, I get #Error. If I put it in the Report
footer, I get the same message, Data type mismatch in criteria expression.

Unless you see where I made a mistake, this might be above me.

--
HHH

As Rick has pointed out the date/time data type is not intended for storing
time duration values. It is in fact a 64 bit floating number under the skin
and implements date/time values as an offset from 30 December 1899 00:00:00.
When you insert a time you are in fact inserting a number of less than 1
corresponding to a point of time on that date.

If you have used a date/time data type column in your table you can sum the
values, but where the sum exceeds 23:59:59 you'll get a date/time value on 31
December 1899 or later, which if formatted as a time will just show the time
of day on that date. You could return it as a time duration value as a
string, however, using the following function, which I've amended slightly
from one which returns the duration between two date/time values:

Public Function TimeDuration( _
dblDuration As Double, _
Optional blnShowDays As Boolean = False) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String

'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")

If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY

TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If

End Function

Paste the above into a standard module in your database and in your report
call it in an expression used as the ControlSource of a text box in a group
or report footer:

=TimeDuration(Sum([Duration]))

If you want to show values of 24 hours or more as # day(s) hh:nn:ss rather
than as hh:nn:ss (with the hours showing as 24, 25 etc) then pass True as the
second optional argument:

=TimeDuration(Sum([Duration]),True)

If you should ever need to insert an individual duration of 24 hours or more
into your table there would be no way you could so with a date/time data type
of course. A solution in such cases would be to have separate columns in the
table for hours, minutes and seconds and combine the values (hours*3600 +
minutes*60 + seconds) to give a total in seconds which you can use for
computations, then format the result back to a hh:nn:ss format by means of a
function which makes use of integer division and the Mod operator.

Ken Sheridan
Stafford, England

Hi,
In my report, I want to sum the time for a particular item. I have a field,
Duration, and it can have anywhere from 5 to 19 lines of time (short time).
Nothing I try seems to work.
Thanks