DateDiff to get average time without weekends

[tfulcher]

I know this question has been addressed before in these forums, but I haven't seen a post that asks the question in the same way I am. I have a query that calculates the differences between several different dates/times in my database. For instance, for each patient who comes in, our database records the date/time they arrived and the date/time that their exam report was dictated.

So, I have used the DateDiff function in the following way to calculate the amount of time in hours between the two exam milestones:

DictationHours: DateDiff("n",[COMPLETED_DT],[DICTATED_DT])/60

The problem, of course, is that we don't want to include weekend hours in our calculations, since we have no doctors working on weekends to dictate reports. So, I need to calculate the difference between the two dates while excluding weekend hours. Every example I can find on this forum deals strictly with the number of non-weekend DAYS elapsed between two dates. Can anyone help me to make the necessary adjustments to give me hours instead of days?

 

[David R]

Does every doctor always work the same schedule, i.e. 8-5 with an hour lunch 12-1? And is any time in that 8-hour span to be included in the elapsed time?

If so you might be able to figure out how many workdays are between the two dates, then figure out the working hours difference between the two times and multiply for your result. Otherwise I'm not sure how the code will know when a doctor _could_ have been working on dictation (presumably your objective). What if they are in meetings, or seeing a patient, or away for a conference?

 

[tfulcher]

We don't really care which doctor is reading the study or whether a doctor is on lunch or anything like that. Our schedule for our offices is always the same. There is always a doctor on site during business hours, but we want to include non-business hours in the calculations, just not weekend hours. In other words, if it takes 21 hours for a report to be approved, we want to know that, but we don't want to know that it took 68 hours because the exam was completed on Friday but the report was approved on Monday.

 

[David R]

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=19424 should do what you want then.

David R