Here is a function that returns the number of working days between two dates.
It excludes Saturdays, Sundays, and any date with an entry in the Holidays
table:
'---------------------------------------------------------------------------------------
' 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 table
'---------------------------------------------------------------------------------------
'
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, vbSaturday) + _
DateDiff("ww", dtmStart, dtmEnd, vbSunday)) + 1
'Fix bug found where months starting on Saturday or Sunday always over state
by one day
If Weekday(dtmStart, vbMonday) > 5 And Day(dtmStart) = 1 Then
CalcWorkDays = CalcWorkDays - 1
End If
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_HOLIDAY_LIST",
"[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
For your purposes, an unpublished date would be treated like a holiday;
however, do not put any saturdays or sundays in the table or they will be
deducted form the total number of days twice.
I did do an application once based on this code where sometimes a Saturday
was a working day, so I modified it to have a flag field in the table to
determine if it was a workday or not, but I no longer have that code.
Here is a function for counting the number of a certian weekday withing a
range. It does not take into account whether that date is in the holiday
table:
'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : StartDate The first date to look for a given weekday
' : EndDate The last date to look for a given weekday
' : DayOfWeek The Day of the week Where Sunday = 1 and
Saturday = 7
' : Can use vb Date constants vbSunday, vbMonday, vbTuesday, etc.
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs in the date range
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(StartDate As Date, EndDate As Date, _
DayOfWeek As Long) As Long
Dim dtmDate As Date
On Error GoTo CountWeekDays_Error
dtmDate = StartDate
Do While dtmDate <= EndDate
If Weekday(dtmDate) = DayOfWeek Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop
CountWeekDays_Exit:
On Error GoTo 0
Exit Function
CountWeekDays_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit
End Function
--
Dave Hargis, Microsoft Access MVP
D-Lys said:
Hello,
I am building a database where I can keep track of customers reservations
for all kind of publications (monthly, yearly, daily and weekend issues)
which are delivered everyday and billed monthly. Is there anyway I can put a
formula in my query to count weekdays of the month (less holidays, less
weekends, less unpublished days). I also need a formula where I can count
separately days (i.e. Saturdays, sundays or sometime Fridays of that same
billing month). Your help would be greatly appreciated.
D-Lys
