With a table of holidays and A97's date fn's - how best to count weekends and holidays between two dates

With a table of holidays and A97's date fn's - how best to count
weekends and holidays between two dates? My holiday table
has 4 fields. I will be adding records to it each year as info
becomes known.

What approach would you advise to tally up the number
of holidays, saturdays and sundays between any 2 dates
in the range of my records in this table?


HoliDate HolEvent HoliWho HoliWeekDay
1/2/2006 New Year's Day Both Monday
1/16/2006 Martin Luther King, Jr. Birthday Both Monday
2/20/2006 Washington's Birthday Feds Monday
4/14/2006 Good Friday State Friday
5/29/2006 Memorial Day Both Monday
7/4/2006 Independence Day Both Tuesday
9/4/2006 Labor Day Both Monday
10/9/2006 Columbus Day Feds Monday
11/10/2006 Veterans Day Both Friday
11/23/2006 Thanksgiving Day State Thursday
11/24/2006 Thanksgiving Both Friday
12/25/2006 Christmas Day Both Monday
12/26/2006 Christmas State Tuesday
1/1/2007 New Year's Day Both Monday
1/15/2007 Martin Luther King, Jr. Birthday Both Monday
2/19/2007 Washington's Birthday Feds Monday
4/6/2007 Good Friday State Friday
5/28/2007 Memorial Day Both Monday
7/4/2007 Independence Day Both Wednesday
9/3/2007 Labor Day Both Monday
10/8/2007 Columbus Day Feds Monday
11/12/2007 Veterans Day Both Monday
11/22/2007 Thanksgiving Day Both Thursday
11/23/2007 Thanksgiving State Friday
12/24/2007 Christmas State Monday
12/25/2007 Christmas Day Both Tuesday
12/26/2007 Christmas State Wednesday

re: With a table of holidays and A97's date fn's - how best to count weekends and holidays between two dates

I found the following in my own search. It seems to work.
It returns workdays. All I have to do get weekend and
holidays is subtract the 10 workdays from the 16 days
under consideration, leaving 6 weekend/holiday days.
Unless anyone can recommend something simpleer, (or
see any flaw in this logic), I'll stick wiith it.

?Workdays(#3/31/2006#, #4/15/2006#)
10


Option Compare Database
Option Explicit

Public Function Workdays(BeginDate, EndDate) As Integer

'Calculate the number of working days between two dates ..
' .. not counting weekend days
' .. and not counting holidays (listed manually in "Holidays" table).

Dim Interim, BD, ED, Diff As Integer

'Check for a NULL entry, which would give runtime error.
'------------------------------------------------------

If IsNull(BeginDate) Or IsNull(EndDate) Then
Exit Function
'This returns "0", so may want to trap IsNull
'and set Result = NULL on calling the function.
End If

'Calc weekdays between the dates (formula modified from A.R. 8/89)
'-----------------------------------------------------------------

BD = (BeginDate Mod 7) - 2
If BD < 0 Then BD = 0

ED = (EndDate Mod 7) - 1
If ED < 0 Then ED = 0

Interim = (Int(EndDate / 7) - Int(BeginDate / 7)) * 5

Diff = Interim - BD + ED

'Subtract holidays (from the Holiday table), if the holiday is between
' the dates and is not a weekend day.
' (proc modified from Lima - Dvlpg Pdox4 Applns 93)
'----------------------------------------------------------------------

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblHolidays")

rst.MoveFirst
Do Until rst.EOF
'Join 2 lines below to 1 and remove '_' character
If (rst!HoliDate >= BeginDate) And (rst!HoliDate <= EndDate) _
And ((WeekDay(rst!HoliDate - 1)) Mod 6 <0) Then
Diff = Diff - 1
End If
rst.MoveNext
Loop
rst.Close

Set dbs = Nothing

Workdays = Diff 'Return value to the function

End Function

re: With a table of holidays and A97's date fn's - how best to count weekends and holidays between two dates

An alternative:

1) Create table of holidays that would be workdays. (Example: If
Christmas is on sunday put the 26th as the holiday instead of the 25th)

And then use following:

'--------------------------------------------------------------------------*-------------

' 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


CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
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


Ron