Below is some code I have lying around (had to find it), that figures out

if a date is a holiday (US only). You could add a check to this function

that considers a Saturday and/or Sunday as a "holiday" and then this

function could be used to count WorkDays.

--

Danny J. Lesandrini

(e-mail address removed)

Option Compare Database

Option Explicit

Public Function IsKnownHoliday(ByVal DateVal As Date) As Boolean

On Error Resume Next

Dim intDay As Integer

Dim intMonth As Integer

intDay = Day(DateVal)

intMonth = Month(DateVal)

IsKnownHoliday = False

Select Case intMonth

Case 1 'New Years Day

If intDay = 1 Then IsKnownHoliday = True

Case 5 'Memorial Day

IsKnownHoliday = (USMemorialDay(DateVal) = DateVal)

Case 7 'July 4th

If intDay = 4 Then IsKnownHoliday = True

Case 9 'Labor Day

IsKnownHoliday = (USLaborDay(DateVal) = DateVal)

Case 11 'Thanksgiving ... and day after Thanksgiving

If (USThanksgivingDay(DateVal) = DateVal) Then

IsKnownHoliday = True

ElseIf (USThanksgivingDay(DateVal) + 1 = DateVal) Then

IsKnownHoliday = True

End If

Case 12 'Christmas

If intDay = 25 Then IsKnownHoliday = True

End Select

End Function

Public Function USThanksgivingDay(WorkDay As Date)

' Return the date of Thanksgiving

' Thanksgiving Day, the fourth Thursday in November.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)

' Set tmpDay to latest possible date that holiday can occur:

' November 30

tmpDay = DateSerial(Y, 11, 30)

' Subtract a number from 0 to 6 to get the right day of the week.

' The "+ 2" term changes depending on the day of the week desired

' in the result:

' Sunday: +6; Monday: +5; ... ; Saturday: +0

tmpDay = tmpDay - ((tmpDay + 2) Mod 7)

USThanksgivingDay = tmpDay

End Function

Public Function USMemorialDay(WorkDay As Date)

' Return the date of Memorial Day

' Memorial Day, the last Monday in May.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)

' Set tmpDay to latest possible date that holiday can occur:

' May 31

tmpDay = DateSerial(Y, 5, 31)

' Subtract a number from 0 to 6 to get the right day of the week.

' The "+ 2" term changes depending on the day of the week desired

' in the result:

' Sunday: +6; Monday: +5; ... ; Saturday: +0

tmpDay = tmpDay - ((tmpDay + 5) Mod 7)

USMemorialDay = tmpDay

End Function

Public Function USLaborDay(WorkDay As Date)

' Return the date of Labor Day

' Labor Day, the first Monday in September.

Dim Y As Integer, tmpDay As Date

Y = Year(WorkDay)

' Set tmpDay to latest possible date that holiday can occur:

' September 7

tmpDay = DateSerial(Y, 9, 7)

' Subtract a number from 0 to 6 to get the right day of the week.

' The "+ 2" term changes depending on the day of the week desired

' in the result:

' Sunday: +6; Monday: +5; ... ; Saturday: +0

tmpDay = tmpDay - ((tmpDay + 5) Mod 7)

USLaborDay = tmpDay

End Function

www.amazecreations.com