Business Days Module

[esi]

Using the following code to calculate business days excluding weekends and
holidays. The way the code calculates, anything w/ today's date is 1
business day. I want to list anything w/ today's date as zero business
days. How would I modify the code to make this change? Would it be as
simple as using this function and subtracting 1?

Public Function GetWorkDays(dtStartDate As Date, dtEndDate As Date) As
Integer
Dim intDaysToSaturday As Integer
Dim intDaysToSunday As Integer
Dim intWeeks As Integer
Dim intWorkDays As Integer
Dim dtX As Date

'Count work days from start date until Saturday of that week.
intDaysToSaturday = vbSaturday - WeekDay(dtStartDate, vbSunday)
dtX = DateAdd("d", intDaysToSaturday + 1, dtStartDate)
If intDaysToSaturday >= 5 Then
intWorkDays = 5
Else
intWorkDays = intDaysToSaturday
End If

'Count number of weeks between start date and end date, not counting
'the last week.
intWeeks = DateDiff("ww", dtX, dtEndDate, vbSunday)

'Count number of work days in the last week.
intDaysToSunday = WeekDay(dtEndDate, vbSunday) - vbSunday
If intDaysToSunday >= 5 Then
intWorkDays = intWorkDays + 5
Else
intWorkDays = intWorkDays + intDaysToSunday
End If

'Multiply in all the weeks inbetween the first week and last week.
intWorkDays = intWorkDays + (intWeeks * 5)

'Subtract out Holidays
intWorkDays = intWorkDays - DCount("*", "Holidays", "HDate >= #" &
dtStartDate & _
"# AND HDate <= #" & dtEndDate & "#")

GetWorkDays = intWorkDays

End Function

 

[Bryan Reich [MSFT]]

Hoping you can clarify: are you not wanting to include just today? (as in
the current day that it is), or are you looking to not include the dtEndDate
(which may or may not be today)?