Date & Time Calculations in Queries

[Brian]

Hello,
I'm trying to generate a report from 'Access' that will
show response times and the time spent on site by
Employees: -

E.g.
Time of telephone call 23:45
Arrival on site 00:02
Response time = 17
Departure from site = 01:12
Billable time = 70 (or 1 hr 10 mins)

The times are currently being entered into a table and I
would like to add a calculation field in a query that
works out the 'Response time' and the 'Billable time'.

I would also like to set a 'desired' response time of 20
minutes and have any late responses highlighted if
possible.

Finally,
A report would be produced within a certain date range
showing the 'average' response time for the month.

Thanks for any advice/tips in advance
Brian.

 

[Allen Browne]

DateDiff() gives the number of minutes between 2 times.

It may be best to include both the date and the time in your fields, so it
works correctly across day boundaries. Otherwise you would need to use IIf()
to handle the day boundaries.

For general tips on calculated time fields see:
http://allenbrowne.com/casu-13.html

To highlight times that exceed 20 minutes on a report, use Conditional
Formatting.

You can calculate sums and averages in the Report Footer section.