# Thread: Calculating Time in Hold

1. Registered User
Join Date
Sep 2002
Location
Ann Arbor, MI
Posts
47

## Unanswered: Calculating Time in Hold

I have a report based on a query (below) that reports the Average Time Spent in QA-Hold. Time is recorded in a table as Date and Time, and pulled out and seperated into hoursdays, hourshours, and hoursminutes, sumed for each record, than averaged.
My problem is that I want to exclude weekends, and also possibly nights(say 1800 to 0500). How can I do this? Thanks, Keith

SELECT [QC Log].[Part#], [QC Log].Quan, [QC Log].DateQC, [QC Log].[INSR#], [QC Log].[Accept/Reject], [QC Log].Vendor, [QC Log].DateReceived, [QC Log].TimeRecieved, [QC Log].TimeQC, 24*([DateQC]-[DateReceived]) AS hoursdays, Hour([TimeQC])-Hour([TimeRecieved]) AS hourshour, Minute([TimeQC])-Minute([TimeRecieved]) AS Expr2, [Expr2]/60 AS hoursminute, [hoursdays]+[hourshour]+[hoursminute] AS TotalTime
FROM [QC Log]
WHERE ((([QC Log].DateQC) Between [Forms]![DateRange].[Start] And [Forms]![DateRange].[End]))
ORDER BY [QC Log].DateQC, [QC Log].[INSR#];

2. Registered User
Join Date
Nov 2002
Posts
150

## Re: Calculating Time in Hold

You can use the weekday function to eliminate weekends: WHERE SOMEDATE <> WEEKDAY(1) AND SOMEDATE <> WEEKDAY(7)

For time range, have you tried using the hour function: WHERE HOUR(SOMETIME) < 18 AND HOUR(SOMETIME) > 5.

3. Registered User
Join Date
Sep 2002
Location
Ann Arbor, MI
Posts
47

## Re: Calculating Time in Hold

Originally posted by FirstAndGoal4
You can use the weekday function to eliminate weekends: WHERE SOMEDATE <> WEEKDAY(1) AND SOMEDATE <> WEEKDAY(7)

For time range, have you tried using the hour function: WHERE HOUR(SOMETIME) < 18 AND HOUR(SOMETIME) > 5.
Thanks, looks like this will filter records. To be more precise, what I wan't to do is eliminate weekends and nights in my calculation of time spent in the Hold. If a part comes in on Friday at 1200, and goes out on Monday at 1200, my query would return 72 hours. I want it to reurn 8 hours if I excluded nights from 1600 to 0800 -- or 24 hours if I excluded only Sat. and Sun. Could these functions somehow be used to accomplish this?

4. Registered User
Join Date
Nov 2002
Posts
150

## Re: Calculating Time in Hold

Originally posted by NightZen
Thanks, looks like this will filter records. To be more precise, what I wan't to do is eliminate weekends and nights in my calculation of time spent in the Hold. If a part comes in on Friday at 1200, and goes out on Monday at 1200, my query would return 72 hours. I want it to reurn 8 hours if I excluded nights from 1600 to 0800 -- or 24 hours if I excluded only Sat. and Sun. Could these functions somehow be used to accomplish this?
Ish. Don't think you can accomplish this with a simple query. You will probably have to write a function to figure all that out.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•