Question

Q: summing total time in HH:MM:SS format in page footer This thread is locked from future replies

I have a report that gives me the total seconds worked on several accounts. I would like to be able to get the grand total of time in HH:MM:SS format at the bottom of the page(page footer), but I do not know how to go about doing this...

Can some one help me?

Thanks


* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Date/Time fields aren't really very good for storing durations. A Date/Time is actually stored as a double float number, a count of days and fractions of a day (times, e.g. 6pm = 0.75) since midnight, December 30, 1899. As such, a total time exceeding 24 hours will not display as you expect - 31 hour 20 minutes is actually #12/31/1899 05:20:00#!

 

How are you storing the individual time worked? You may want to consider storing the date and time started and ended for each "bout" of work, and calculate the duration worked using an expression like

DateDiff("s", [TimeStarted], [TimeEnded])

 

You can display it in a footer in this format using the rather ugly but functional expression:

 

Sum([duration]) \ 3600 & Format(Sum([duration]) \ 60 MOD 60, ":00") & Format(Sum([duration]) MOD 60, ":00")

 

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.


You will have a difficult time displaying any sums in a Page Footer. Typically you should use Group or Report Footer sections for totals.

 

If you really want to use Page Footer totals, you may need to rely on Running Sums with some code.

Duane Hookom
Minnesota

Did this solve your problem?

Sorry this didn't help.


To get a total to appear in the page footer, you need to use a (hidden?) text box in the appropriate group or report header section.  Then the page footer text box expression can refer to the header text box to display the total.

Did this solve your problem?

Sorry this didn't help.


I have a query with two fields:

1) "diffInSec" is storing the time in Seconds

2) "Yourtime" is storing the time in HH:MM:SS format.

 

I do want the total time to go into a group footer, but from what I have been reading, it appears using the "seconds" field would be better --I think?

Did this solve your problem?

Sorry this didn't help.


It is possible to sum date/time values which represent a duration and return the total in the format hh:nn;ss by using the following function:

Public Function TimeElapsed(dtmTime As Date, _
            Optional blnShowdays As Boolean = False) As String

    ' Returns a date/time value as a duration
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeElapsed = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeElapsed = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If

End Function

If the YourTime column returned by your query is a formatted value it will be text, so to sum it you'd first need to convert it to a date/time value, and then format the result with the above function:

=TimeElapsed(Sum(CDate([YourTime])))

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.


Hi Ken

Please bare with me since I am not the sharpest pencil in the box when it comes to this stuff.

I created the "Yourtime" field by using the following formula I found:

format(Int([DiffinSec]/360) & ":" & int(Int([diffinSec])/60-Int([DiffinSec]/360)*60) & ":" Int([DiffInSec] mod 60), "HH:MM:SS")

I looked at the properties and I do not see where I can change the format--if at all, so I am lost when it comes to the "format" item you mentioned for the "yourtime" field.

Can you elaborate..?


Did this solve your problem?

Sorry this didn't help.


If you use a function like mine you can cut out the need for all that arithmetic in an expression.  Say you have two columns, DatetimeStart and DateTimeEnd in the underlying table.  You can return those column sin the report's query and as the ControlSource property of an expression put:

=TimeElapsed([DateTimeEnd]-[DateTimeStart])

to show the elapsed time between the two date/time values in the format hh:nn:ss

In a group or report footer in the report you can sum the times with:

=TimeElapsed(Sum([DateTimeEnd]-[DateTimeStart]))

Or you can call the function in the query:

YourTime:TimeElapsed([DateTimeEnd]-[DateTimeStart])

Then simply bind a text box in the report to the computed YourTime column.  If you want to sum these times, however, the YourTime column returns a string expression, so you need to convert it to a date/time value first before summing it:

=TimeElapsed(Sum(Cdate([YourTime])))

A date/time value under the skin is no more than a number in which the integer part represents days and the fractional part the times of day.  Date/time zero is in Access 30 December 1899 00:00:00, so the number stored for any date/time value is simply the duration in days since then.  Access just presents it in a date/time format.  You can do arithmetic on the underlying numbers until the cows come home; you just have to present the final result in a time format, which is all that my function does, by default as hh:nn:ss, or optionally as d:hh:nn:ss.

You can see how things work in the debug window (aka immediate window), firstly by returning the current date/time as a number:

? CDec(Now())
 41223.5514583333

which is the number of days from 30 December 1899 00:00:00,at the time I entered the above.

? TimeElapsed(Now())
989365:15:26

which is the elapsed time since 30 December 1899 00:00:00 in hours:minutes:seconds

? TimeElapsed(Now(),True)
41223:13:16:44

which is the elapsed time since 30 December 1899 00:00:00 in days:hours:minutes:seconds

I was born on 1 November 1946 at 6.00 AM, so:

? TimeElapsed(Now()-#1946-11-01 06:00:00#)
578791:19:11

is how old I am in hours:minutes:seconds.

Of course each of the values returned above will be slightly different because a few more seconds have ticked away between my entering each one in the debug window.

Finally, one more little trick.  Sometimes people enter times without dates, e.g. the beginning and end of a shift.  This is fine if the shift starts and ends in the same day, but if a shift spans midnight, e.g. 10.30 PM to 6.00 AM  then if we use my TimeElapsed function:

? TimeElapsed(#06:00:00# - #22:30:00#)
-08:30:00

we get the wrong result and a negative value as Access has no way of knowing that the later time is in fact the earlier time.  Another little function caters for that:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String
           
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
   
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
   
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
   
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
   
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
   
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "00") & _
            Format(dtmTime, ":nn:ss")
    End If
   
End Function

So now:

? TimeDuration(#22:30:00#,#06:00:00#)
07:30:00

we get the correct result.  Note that this function takes the times as two separate arguments.  It will work with full date time values too:

? TimeDuration(#1946-11-01 06:00:00#,Now())
578791:32:35

I've aged  by 13 minutes and 19 seconds since I returned my current age above.  How time flies when you're having fun!

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.


Ken

Dude, you’re awesome.

I appreciate the time you took to explain things.

It only took me about an hour to figure out I needed to place your function in a module for thing to work--but when things come together, wow it’s great!

 

Thanks again for all the tidbits...

 

Did this solve your problem?

Sorry this didn't help.


Ken

 

I have Added a text box "CaseCount" in the Group footer and set the control source to =1 and the running sum to "Over All"

I am trying to count the total records and divide it by the total hours to get the average time per record to show up in the page footer.

 

When I tried to combine the "TimeElapsed" with the "Casecount" I get an error message indicating I can not have an aggregated function in expression.

 

How can I solve this?

 

 

 

 

Did this solve your problem?

Sorry this didn't help.


You have to do the computation in a control in the report footer.  The control can be hidden by setting its Visible property to False (No).  Then reference that control in the ControlSource of another control in the page footer.  So the ControlSource property of a text box txtAvgTimeHidden  in the report footer might be:

=TimeElapsed(Sum([DateTimeEnd]-[DateTimeStart])/[CaseCount])

and the ControlSource of a visible txtAvgTime control in the page footer would be:

=[txtAvgTimeHidden]

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.


* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
Question Info

Views: 1,833 Last updated: April 19, 2018 Applies to: