DateDiff excluding weekends

  • Thread starter Thread starter cru
  • Start date Start date
C

cru

Does anyone know how to exclude weekends from DateDiff function?

My formula is:

#Days to Refund: DateDiff("d",[EntryDate],[StatusDate])

Thanks
 
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 & "#")
 
Back
Top