DateDiff in Access Excluding weekends

Divide (div?) datedif by 7. Take that result and multiply by 2.

Then take the remainder (mod I believe?) of that division and execute a for loop (Note: this is not coded exactly, just pseudocode to give you an idea)

For i=0 to Remainder
if weekday(DateCompleted-i)=1 or weekday(DateCompleted-i)=7 then
counter=counter+1
end if
next i

This will check the last day and 6 or less days prior if they are a Saturday or Sunday.

Then you have
DateDiff - ((DateDiff div 7)*2) - counter

That should give you your number of weekdays. Then multiply by 8 hours.

There may be an easier way, but this should work.

Hi KS
I haven't used the code in Access, but it looks right to me. :-)
Sorry that I couldn't figure out a neater way of doing it, but it always seems to depend on whether your start/finish days are a weekend day and which weekend day it is...
You could always return each day to a check of a weekend, counting the days in a loop (and multiplying by 8 to get your hours) - but this might only be more efficient if the date difference between dates was expected to usually be small.

To call the function - use this code statement (check for an error return value of -1):
intWorkHoursBetween([DateReferred],[DateCompleted])

This is the code for your function:

Public Function intWorkHoursBetween (strThisDate as String, strThatDate as String) As Integer

On Error GoTo intWorkHoursBetween_Err

dim intAddDays as Integer

intAddDays = 0

IF WeekDay(strThisDate) = vbSunday then intAddDays = intAddDays - 1
IF WeekDay(strThisDate) = vbSaturday then intAddDays = intAddDays - 2
IF WeekDay(strThatDate) = vbSunday then intAddDays = intAddDays - 2
IF WeekDay(strThatDate) = vbSaturday then intAddDays = intAddDays - 1

IF WeekDay(strThisDate) <> 1 AND WeekDay(strThisDate) <> 7 AND WeekDay(strThatDate) <> 1 AND WeekDay(strThatDate) <> 7 then intAddDays = intAddDays + 1

intWorkHoursBetween = ((DateDiff(strThisDate , strThatDate, "ww")*5) + intAddDays)*8
Exit

intWorkHoursBetween_Err
intWorkHoursBetween = -1
End Function

I created following function:
Dim day1, day2, week1, week2, weeks

Function Workdays(date1, date2)
day1 = Weekday(date1)
Select Case day1
Case 1: date1 = date1 + 1
Case 7: date1 = date1 + 2
End Select
day2 = Weekday(date2)
Select Case day2
Case 1: date2 = date2 - 2
Case 7: date2 = date2 - 1
End Select
week1 = Val(Format(date1, "ww"))
week2 = Val(Format(date2, "ww"))
weeks = week2 - week1
day1 = Weekday(date1)
day2 = Weekday(date2)
Workdays = weeks * 5 + day2 - day1 + 1
End Function
This method calculates in whole days, you only have to add functionality on the time aspect.

Peter