You can't do it with DateDiff. Here is a function that does it:
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
On Error GoTo CalcWorkDays_Error
'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoludays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")
CalcWorkDays_Exit:
On Error Resume Next
Exit Function
CalcWorkDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit
End Function
Note that it is also written to exclude holidays. It does require a table
has a record for each holiday you observe. If you don't want to exclude
holidays, either delete or comment out this line:
CalcWorkDays = CalcWorkDays - DCount("*", "tblHoludays", "[holidate]
between #" _
& dtmStart & "# And #" & dtmEnd & "#")