Calculate Average Times

[Dan]

Hi !

I wonder if anyone can help, I'm at a loss at how to go about the following.
We have an Access Database into which we log calls logged by our users, each
call is assigned to a Technician and is time stamped with a Call Open Date
and a Call Closure Date once completed. I wish to be able to run a report
that will work out the total calls logged to each Technician, then for each
one work out the average time a call is open using the Call Open and Closure
Dates.

Can anyone give me any pointer on how to go about working out the difference
between each date as a time period i.e. Number of Days, Hours, Mins, Secs
and then add these together before working out the average using the total
calls logged ? I'm having a total mental block here and cannot figure out
how to do this. Any help would be greatly appreciated !

Kind Regards

Dan

 

[Les]

Check through the built-in functions of access. Datediff
might work for you. You can specify different time period
criteria. You may be able to find another function that
works too.

Hope this helps.

 

[John Spencer (MVP)]

SELECT Technician,
Format(CDate(Avg(DateDiff("s",OpenDate,CloseDate))/86400),"dd hh:nn:ss") as AvTime
FROM YourTable
Where OpenDate Between #1/1/03# And #1/31/03#
AND CloseDate is Not Null
Group By Technician

This is somewhat problematic in that it will fail if the days exceeds 31 and it
may fail anyway as this was a hack off the top of my head.

 

[Dan]

Hi John

Many thanks for your reply, it's greatly appreciated. It all looks good,
one question, you divide the DateDiff result by 86400, can I ask what this
figure represents ?

Many thanks

Dan

 

[Douglas J. Steele]

86400 is the number of seconds in a day (24 hours/day*60 minutes/hour*60
seconds/minute = 86400 seconds/day).

What John's trying to do is take advantage of the fact that dates are stored
as 8 byte floating point values, where the integer part represents the
number of days since 30 Dec, 1899, and the decimal part represents the time
as a fraction of a day. Unfortunately, it won't work. That's because he's
trying to get 1 day if the number of seconds is betwewen 86401 and 172800, 2
days if the number of seconds is between 172801 and 259200 and so on. Due to
how dates are stored, you're actually going to get 31 days in the first
case, 1 day in the second case and so on.

What you need to do is write your own function to format the number of
seconds into d hh:mm:ss format, and use that function.

FormatTimeInSeconds(Avg(DateDiff("s",OpenDate,CloseDate))) as AvTime

where FormatTimeInSeconds is something like

Function FormatTimeInSeconds(TimeInSeconds As Long) As String
Dim lngDays As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
Dim lngSecondsRemaining As Long
Dim strFormattedTime As String

' 86400 is the number of seconds in a day: 24 * 60 * 60

lngDays = TimeInSeconds \ 86400
lngSecondsRemaining = TimeInSeconds - (lngDays * 86400)
lngHours = lngSecondsRemaining \ 3600
lngSecondsRemaining = lngSecondsRemaining - (lngHours * 3600)
lngMinutes = lngSecondsRemaining \ 60
lngSeconds = lngSecondsRemaining - (lngMinutes * 60)

Select Case lngDays
Case 0
strFormattedTime = vbNullString
Case 1
strFormattedTime = "1 day "
Case Else
strFormattedTime = Format$(lngDays, "0") & "days "
End Select

FormatTimeInSeconds = strFormattedTime & Format$(lngHours, "00") & _
":" & Format$(lngMinutes, "00") & ":" & _
Format$(lngSeconds, "00")

End Function


From the Debug window:

?Format(CDate(DateDiff("s",#1/1/2004 8:00:00#,#1/2/2004
10:00:00#)/86400),"dd hh:nn:ss")
31 02:00:00
?FormatTimeInSeconds(DateDiff("s",#1/1/2004 8:00:00#,#1/2/2004 10:00:00#))
1 day 02:00:00

 

[John Spencer (MVP)]

Doug,

Darn! I was trying to be lazy and use a hack. Should have known better. Thanks
for stepping in.

John S

 

[Dan]

Hi Guys !

Many thanks for both your help, it's greatly appreciated !

Regards

Dan