General discussion


Date query based on business hours

By rhowens ·
I need to create an Access query that pulls data based on an open_date and closed_date. I compute the time it takes to close a ticket on these two fields with the datediff command. The problem is the users do not work 24/7 and the time that the users are not working should not be considered. I need to calculate the hours it takes to close a ticket but not consider non-working hours. Any ideas?


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Been a while, but.....

by awfernald In reply to Date query based on busin ...

Here's how we resolved this problem:

1. Create a "holiday" file, which lists all the days that would normally be workdays, but are not because of holidays.
2. Create a script that counts the number of workdays between open_date and closed_date (I did this once about 10 years ago, so... don't ask me to repeat please ;p~)
3. Determine how many holidays are between open_date and closed_date based upon the info in the holiday file.
4. You should now have a figure between 0 (closed the same day as opened), and the actual number of days.
5. If answer = 0, then time = closed_date-open_date (in time of course, not days), if answer > 0, then time = (closed_date_endworkinghours - closed_date) + (open_date - open_date_startworkinghours) + answer * normal_working_hours_inaday.

If noone else answers that has a prepared script, I'll check it out again, and see if I can tell you the logic behind the number of working days between two dates.

Related Discussions

Related Forums