Help Needed Reporting Elapsed Time. 1

[Bennie47250]

Using Access 2002

We have a home grown help desk package and I have been asked to write a few reports off of the existing data. I’m having problems calculating the elapsed time.

The table has start_time and end_time fields. These fields are defined as Date/Time fields. Their format is set to “General Date” and the data looks like this 08/25/2003 8:52:18 AM.

What I have been asked to do is develop reports that will show the time spent on each call. The total time spent on a group of call types and the total time spent on all the calls for the week, month year.....

For the example of a single call, would like to see it like 1 hour 32 minutes 15 seconds.

In the example of calls for a group, would like to see it as 41 hour 15 minutes 32 seconds.

In the report footer, where the total time is reported would like to see it expressed in the same total hours, minutes and seconds.

I have experimented with some formulas but I’m not getting the desired results and I’m looking for some help.

Thanks

 

[SgtJarrow]

DateDiff is the function to determine elapsed time....in you case you would evaluate to see how many second passed, and divide by appropriate numbers to get your minutes and hours.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Bennie47250]

I can see how DateDiff would get the total # of seconds for the individual calls but will it also work over a group or the report total?

Not sure how to take the total # of seconds and show it in the desired format also

 

[SgtJarrow]

You would include the datediff calculation as a field in the query which is the basis for your report. This field is also display on the report. You include a running sum in the report to get your "group" totals and display this in the group/report footer.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Bennie47250]

Ahh I see.

Now how would I go about taking the # of seconds and show it in Hours, Minutes and Seconds?

 

[SgtJarrow]

You can probably find some functions here for that conversion....I don't have any and I can't think of any built into Access....Just do a search for converting time or something

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Bennie47250]

Found this formula that will convert the total # of seconds to the format I'm looking for.

Expr3: [TotalSecs]\3600 & " Hour(s), " & Format(([TotalSecs] Mod 3600)\60,"00&quot & " Minutes, " & Format([TotalSecs] Mod 60,"00&quot & " Seconds"

The results look like "1 Hour(s), 09 Minutes, 23 Seconds" and 0 Hour(s), 00 Minutes, 54 Seconds.

Now if it would be possible to suppress the 0 Hour(s), 00 Minutes, this would be perfect.

 

[SgtJarrow]

Sure you can....but you are going to have to build it yourself....

Create a function somthing like the following:

Public Function GetElapsedTime(dteStart As Date, dteEnd As Date) as String

Dim bblTotal As Double
Dim dblSeconds As Double
Dim dblMinutes As Double
Dim dblHours As Double
Dim strFinal As String

dblTotal = DateDiff("s", dteStart, dteEnd)

dblHours = dblTotal\3600
dblMinutes = ([TotalSecs] Mod 3600)\60
dblSeconds = [TotalSecs] Mod 60

strFinal = ""
If dblHours <> 0 Then strFinal = strFinal & &quot;, &quot; & CStr(dblHours) & &quot; Hour&quot; & IIF(dblHours>1,&quot;s&quot;,&quot;&quot
If dblMinutes <> 0 Then strFinal = strFinal & &quot;, &quot; & CStr(Format(dblMinutes,&quot;00&quot) & &quot; Minutes&quot;
strFinal = strFinal & &quot;, &quot; & CStr(Format(dblSeconds,&quot;00&quot) & &quot; Seconds&quot;

GetElapsedTime = strFinal

End Function

You may need to play with the declarations some, and even tweak it...but that is the gist. Then use this in your query:

ElapsedTime: =GetElapsedTime([startddatefield],[enddatefield])

You should get the results, or something VERY close, you are looking for.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com