Need help with DateDiff

[tfulcher]

I have what may be an odd question, but I'm sure there's someone out there that knows how to accomplish this.

I have a report that is using DateDiff to calculate the amount of time between two dates. What I want to do is take this one step further. If there is a weekend between the two dates, I would like the query to automatically subtract 48 hours from the calcluation. We run a radiology practice, and we're trying to show our average times of service, like the average amount of time elapsed from the time a patient checks in until the patient's exam report is approved. Since we don't work on weekends, we don't want the extra 48 hours to be included in the calculations.

Any help anyone can give would be most appreciated.

 

[AlanS]

You can do this with a For - Next loop. The initial value will be the start date + 1, and the terminal value will be the end date. On each iteration of the loop, use the Weekday function to determine if that date falls on a weekend. Add 1 day (or 24 hours) to your elapsed time only if the date does not fall on a weekend.

 

[tfulcher]

I'm not exactly sure how to do this. Any chance you can be a little more specific?

 

[AlanS]

Here's an example:

Sub TestIt()
Dim Date1 As Date, Date2 As Date
Date1 = #12/1/01#
Date2 = #1/1/02#
MsgBox "There are " & Weekdays(Date1, Date2) & " weekdays between " & Date1 & " and " & Date2 & "."
End Sub
Function Weekdays(StartDate As Date, EndDate As Date) As Long
'returns number of weekdays between StartDate and EndDate;
'StartDate is excluded from calculation, EndDate is included
Dim Counter As Long, LoopVar As Date
Counter = 0
For LoopVar = StartDate + 1 To EndDate
Select Case WeekDay(LoopVar)
Case vbSaturday, vbSunday 'day falls on a weekend, so do nothing
Case Else: Counter = Counter + 1 'day is a weekday, so add it to total
End Select
Next LoopVar
Weekdays = Counter
End Function

 

[tfulcher]

Okay, I see what you're doing here. Now, is there a way to modify this so that, rather than giving me a whole number of days, it will give me the number of hours instead? For our report, we want to show the number of hours between certain milestones.

 

[AlanS]

To compute the number of hours instead of the number of days, you'd need to modify the function as follows:

1. make sure that Date1 and Date2 both contain date and time values (e.g., 12/12/01 10:45:32 AM).
2. for each loop iteration except the last one, add 24 to the counter instead of 1.
3. for the last loop iteration, use the following to increment the counter:
sngHours1 = 24 * (Date1 - Int(Date1))
sngHours2 = 24 * (Date2 - Int(Date2))
Counter = Counter + 24 + Clng(sngHours2 - sngHours1)