gcomyn
07-16-2010, 02:56 PM
I've developed the following code to count the business days between 2 dates.
It will not count Saturday or Sunday, and also exclude the dates hardcoded into the array.
I wanted to use a named range for the dates, but couldn't figure it out, so I used an array, that has to either be added to or changed each year.
Here is the code
'---Begin Code---
Public Function Business_Days_Between(datEndDate As Date, datStartDate As Date) As Long
'========================================================================== ===========================
'Written by: Stan Paszt
'Written on: 07/15/2010
'This function will calculate the number of business days between two dates. It includes the dates used.
'It does not count Saturday and Sunday, and there is an internal array for Holidays during the year that it will not count.
'Example:
' Business_Days_Between(#7/12/10#,#7/16/10#)
'returns 5. 12, 13, 14, 15, 16 are all counted as business days.
'========================================================================== ===========================
Dim lngTemp As Long
Dim datTemp As Date
Dim x As Long
Dim varHolidays(1 To 10) As Variant
Dim bolHoliday As Boolean
varHolidays(1) = #1/1/2010# 'New Years Day
varHolidays(2) = #1/18/2010# 'Martin Luther King Day
varHolidays(3) = #2/15/2010# 'Presidents Day
varHolidays(4) = #5/31/2010# 'Memorial Day
varHolidays(5) = #7/5/2010# 'Independence Day
varHolidays(6) = #9/6/2010# 'Labor Day
varHolidays(7) = #10/11/2010# 'Columbus Day
varHolidays(8) = #11/11/2010# 'Veterans Day
varHolidays(9) = #11/25/2010# 'Thanksgiving Day
varHolidays(10) = #12/25/2010# 'Christmas Day
If datEndDate < datStartDate Then
datTemp = datEndDate
datEndDate = datStartDate
datStartDate = datTemp
End If
Do While datStartDate <= datEndDate
If Weekday(datStartDate) <> 1 And Weekday(datStartDate) <> 7 Then
For x = 1 To 10 Step 1
If varHolidays(x) = datStartDate Then
bolHoliday = True
Exit For
Else
bolHoliday = False
End If
Next x
If Not bolHoliday Then
lngTemp = lngTemp + 1
End If
End If
datStartDate = datStartDate + 1
Loop
Business_Days_Between = lngTemp
End Function
'---End Code---
it can probably be streamlined.
GComyn
:beerchug:
It will not count Saturday or Sunday, and also exclude the dates hardcoded into the array.
I wanted to use a named range for the dates, but couldn't figure it out, so I used an array, that has to either be added to or changed each year.
Here is the code
'---Begin Code---
Public Function Business_Days_Between(datEndDate As Date, datStartDate As Date) As Long
'========================================================================== ===========================
'Written by: Stan Paszt
'Written on: 07/15/2010
'This function will calculate the number of business days between two dates. It includes the dates used.
'It does not count Saturday and Sunday, and there is an internal array for Holidays during the year that it will not count.
'Example:
' Business_Days_Between(#7/12/10#,#7/16/10#)
'returns 5. 12, 13, 14, 15, 16 are all counted as business days.
'========================================================================== ===========================
Dim lngTemp As Long
Dim datTemp As Date
Dim x As Long
Dim varHolidays(1 To 10) As Variant
Dim bolHoliday As Boolean
varHolidays(1) = #1/1/2010# 'New Years Day
varHolidays(2) = #1/18/2010# 'Martin Luther King Day
varHolidays(3) = #2/15/2010# 'Presidents Day
varHolidays(4) = #5/31/2010# 'Memorial Day
varHolidays(5) = #7/5/2010# 'Independence Day
varHolidays(6) = #9/6/2010# 'Labor Day
varHolidays(7) = #10/11/2010# 'Columbus Day
varHolidays(8) = #11/11/2010# 'Veterans Day
varHolidays(9) = #11/25/2010# 'Thanksgiving Day
varHolidays(10) = #12/25/2010# 'Christmas Day
If datEndDate < datStartDate Then
datTemp = datEndDate
datEndDate = datStartDate
datStartDate = datTemp
End If
Do While datStartDate <= datEndDate
If Weekday(datStartDate) <> 1 And Weekday(datStartDate) <> 7 Then
For x = 1 To 10 Step 1
If varHolidays(x) = datStartDate Then
bolHoliday = True
Exit For
Else
bolHoliday = False
End If
Next x
If Not bolHoliday Then
lngTemp = lngTemp + 1
End If
End If
datStartDate = datStartDate + 1
Loop
Business_Days_Between = lngTemp
End Function
'---End Code---
it can probably be streamlined.
GComyn
:beerchug: