summing total time in HH:MM:SS format in page footer

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
 

Question Info


Last updated October 16, 2018 Views 2,196 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


=TimeDuration([Time_Start_Period1],[Time_End_Period1])


That's the one!


Thanks Ken (that's a great function you created there)



Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The hash symbol is the date/time delimiter for date literals.  You don't need it when the argument is a column or control name, or a variable.  Try:

    TimeDuration([Time_Start_Period1],[Time_End_Period1])

_____________________
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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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!


Hi Ken,I  tried your 'TimeDuration' function on a form, but in the results text box, I jest get #Name? error.


I have this: ? TimeDuration(#[Time_Start_Period1#],[#Time_End_Period1#])

in my results box.


[Time_Start_Period1] and [Time_End_Period1] have a format of 'Short Time'


I would love to get this working as my 'time' boxes do often straddle midnight.



PS: your =TimeElapsed(Sum([DateTimeEnd]-[DateTimeStart])) works well, but I need the functionality of ? TimeDuration.


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks a lot for your post... It works perfect for me ;).

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Firstly, as you are not aggregating any values over a set, you do not need a GROUP BY clause in this query.

Secondly, you should be using the TimeDuration function, not the TimeElapsed function.  However, this would return the value in time format.  To return it as a number you can create a simpler function, e.g. to return the duration as the number of hours to a precision of two decimal places:

Public Function TimeDurationAsHours(varFrom, varTo)
            
    ' Returns duration between two date/time values
    ' in hours to 2 decimal places
    
    ' 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

    If Not IsNull(varFrom) And Not IsNull(VarTo) Then
        ' subtract one day from 'from' time if later than or same as 'to' time
        If varTo <= varFrom Then
            If Int(varFrom) + Int(varTo) = 0 Then
                varFrom = varFrom - 1
            End If
        End If
    
        ' return duration as nmber of hours rounded to 2 decimal places
        TimeDurationAsHours = Round((varTo - varFrom) * 24, 2)
    End If
    
End Function

You can see how it works in the debug window:

? TimeDurationAsHours(#14:30#,#18:45#)
 4.25

and if the times span midnight:

? TimeDurationAsHours(#20:30#,#02:45#)
 6.25

For an illustration of the use of various time functions see TimeArithmetic.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
_____________________
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.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Just for future reference, please don't piggyback your question on another thread. Start a new thread and if you feel the need, insert a link to the older thread.
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.