Sum time for report

[abenitez77]

I have a report that has 2 fields (TimeIn and TimeOut). I am totaling the time for the day using : =Format(TimeSerial(0,((DateDiff("n",[TimeIn],[TimeOut]))),0),"h:nn")

In my report, I am grouping it by week and I need to sum the time at the report footer for that week. How can i do it? I tried doing Sum(text13). Text13 is the name of the textbox that has the calculation above totaling the time for the day.

 

[dhookom]

I calculate everything based on minutes.

  =DateDiff("n",TimeIn, TimeOut)

Summing this for any group or report can be done using:

  =Sum(DateDiff("n",TimeIn, TimeOut))

If you prefer to keep the time duration in a date/time format, just subtract the TimeIn from the TimeOut.

  =TimeOut-TimeIn

To sum this for any group or report, use:

  =Sum(TimeOut-TimeIn)

The issue with the Sum(TimeOut-TimeIn) is when this expression is greater than 24 hours. If this is the case, you can multiply the integer part of the Sum() times 24 to get the total hours. Then get the Time portion by wrapping the sum in TimeValue().


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

I need to sum the time...

FYI and a bit of a technicality, as Duane implied, "If you prefer to keep the time duration..." there is a difference between TIME, which is a reference POINT, like a DATE, and DURATION.

You cannot sum TIMES meaningfully, just as you cannot sum DATES and get a meaingful result.

You can, however, sum DURATIONS of time, be they in units of seconds, hours, days, centuries.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Skip,
Thanks for the clarification. There is a lot of confusion regarding the date/time field and data type that should be used to store a [red]point in time[/red] versus numeric field that stores a [red]duration of time[/red].

Duane
Hook'D on Access
MS Access MVP