Hi Rich,
See below my sig line.
I am up in Clarion. If you ever need any outside help, contact me at my
email address below. My fees are very reasonable.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
From my file ------
Count The Days Between Two Dates Excluding Weekends And Holidays
Private Function SkipHolidaysA(adtmDates As Variant, _ dtmTemp As Date, _
intIncrement As Integer) As Date
' Skip weekend days, and holidays in the array referred to by adtmDates.
' Return dtmTemp + as many days as it takes to get to a day that's not a
holiday or
' weekend.
' Requires IsWeekEnd Function At The End
Dim strCriteria As String
Dim strFieldName As String
Dim lngItem As Long
Dim blnFound As Boolean
On Error GoTo HandleErrors
' Move up to the first Monday/last Friday, if the first/last of the month
was a weekend
' date.
' Then skip holidays.
' Repeat this entire process until you get to a weekday.
' Unless adtmDates contains an item for every day in the year,
' this should finally converge on a weekday.
Do
Do While IsWeekend(dtmTemp)
dtmTemp = dtmTemp + intIncrement
Loop
Select Case VarType(adtmDates)
Case vbArray + vbDate, vbArray + vbVariant
Do
blnFound = FindItemInArray(dtmTemp, adtmDates)
If blnFound Then
dtmTemp = dtmTemp + intIncrement
End If
Loop Until Not blnFound
Case vbDate
If dtmTemp = adtmDates Then
dtmTemp = dtmTemp + intIncrement
End If
End Select
Loop Until Not IsWeekend(dtmTemp)
ExitHere:
SkipHolidaysA = dtmTemp
Exit Function
HandleErrors:
Resume ExitHere
End Function
Private Function IsWeekend(dtmTemp As Variant) As Boolean
' If weekends aren't Saturday (day 7) and Sunday (day 1), change this
routine to ' return True for whatever days are weekend days.
' Required by: SkipHolidays Function Above
If VarType(dtmTemp) = vbDate Then
Select Case Weekday(dtmTemp)
Case vbSaturday, vbSunday
IsWeekend = True
Case Else IsWeekend = False
End Select
End If
End Function
Rich_In_Pgh said:
Hello to all! I have to calculate in a query the number of days that have
passed between two fields...[DATE_RECEIVED] and [DATE_CLEARED].
I need the calculation to exclude weekend days (SAT(6) and SUN(7) from
the
net result.
EXAMPLE:
Say I have a [DATE_RECEIVED] of OCT-01-2005 and [DATE_CLEARED] of
OTC-12-2005.
Subtracting OCT-12-2005 from OCT-01-2005 yields 11 days when I really need
it to return 8 days. This includes 4 weekend dates and I need it to NOT
include them. I have tried various formulas usuing the weekday
number....the
datepart "w" etc but to no avail. Any help would be greatly appreciated!!
Thank you. Rich
