Date Difference networkdays not accepted in Access.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Want to calculate the number of days between two dates and not include
weekends. Have tried entering -
time: networkdays([Date of entry],[Date completed])

as a query calculation - but this is not
recognised. (Works great in Excel !!) Happy to use VB but looking for
suggestions,

Any ideas much appreciated,
David
 
Here is a function designed for that purpose. It includes reference to a
holiday table so holidays on any non working day not on a weekend can be
excluded. All you need for the holiday table is a date field and a text
descripton field.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
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("*", "holidays", "[holdate] 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
 
Back
Top