Record days lapsed minus the weekends in Access

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

Guest

I have a database that records the number of days for a document to be
closed. The problem is that we do not work on weekends and holidays so I do
not want this days to be counted. Is there a way to do this?
 
Kelly,

Here is a function that will return the number of workdays between two
dates. It will include both the start and end dates in the calculation. For
example, start date is 7/1/2005 and end date is 7/5/2005, it will return 2.
7/1 = work day
7/2 = weekend day
7/3 = weekend day
7/4 = holiday
7/5 = work day

You will need a holiday table if you don't have one. Mine has a Short Date
field [Holdate] and the table name is Holidays. It also as a field
[Holdate_Desc] for a text description of the date

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
 
Back
Top