Business Days Calculations that accounts for holidays 1

[woro2006]

I found somewhere in the forum that I can calculate business days between two days. Suppose I have a table that contains all the holiday day dates (table = tblholiday, field name = hoilday). How can I extend the following function to allow me to account for the holidays in tblholiday. Please write the complete code (if possible), as I am a newbie. Thanks!

Function BusinessDays(dtStartDate As Date, dtEndDate As Date)
'Returns number of business days between two dates as an integer
'Returns -1 if the start date is greater than the end date

Dim intDayCount As Integer
Dim J As Integer
Dim dtNextDate As Date

'----------------------------------------------------------------
'- Set the first date to check -
'----------------------------------------------------------------
dtNextDate = dtStartDate

'----------------------------------------------------------------
'- Return error code of -1 if Start Date is later than End Date -
'----------------------------------------------------------------
If dtStartDate > dtEndDate Then
BusinessDays = -1
Else

'----------------------------------------------------------------
'- Loop through all dates between Start Date and End Date -
'----------------------------------------------------------------
Do Until dtNextDate >= dtEndDate

'----------------------------------------------------------------
'If it's a weekday (Monday to Friday) add 1 to the day counter
'----------------------------------------------------------------
If WeekDay(dtNextDate, vbMonday) <= 5 Then
intDayCount = intDayCount + 1
End If

dtNextDate = dtNextDate + 1
Loop


'------------------------------------------------------------------
'- Return number of business days between the start and end dates -
'------------------------------------------------------------------
BusinessDays = intDayCount

End If

End Function

 

[lespaul]

A quick search of the FAQs turned up:

FAQ181-261
FAQ705-4531



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi