Solved: Count Business Days Between 2 Dates

[gcomyn]

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---

[vba]
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
[/vba]
'---End Code---

it can probably be streamlined.

GComyn

[p45cal]

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.

it can probably be streamlined.

Oh yes...

in xl2007 one line:[vba]Application.WorksheetFunction.NetworkDays(#2/1/2010#, #10/12/2010#, Range("myhols"))[/vba]where the named range myhols contains real excel dates. In tests here it gave the same results as your function

Or if you want to have the holiday dates within the function:[vba]Public Function Business_Days_Between2(datEndDate As Date, datStartDate As Date) As Long
hols = Array(#1/1/2010#, #1/18/2010#, #2/15/2010#, #5/31/2010#, #7/5/2010#, #9/6/2010#, #10/11/2010#, #11/11/2010#, #11/25/2010#, #12/25/2010#)
For i = LBound(hols) To UBound(hols): hols(i) = CLng(hols(i)): Next i
Business_Days_Between2 = Application.WorksheetFunction.NetworkDays(datStartDate, datEndDate, hols)
End Function
[/vba]
In xl2003 it's not so straightforward, but you could use:[vba]x=evaluate("NETWORKDAYS(DATE(2010,2,1),DATE(2010,10,12),myHols)")[/vba]or[vba]
x=[NETWORKDAYS(DATE(2010,2,1),DATE(2010,10,12),myHols)]
[/vba] I'll wait and see which version of Excel you're using before going any further with xl2003.

[gcomyn]

Thanks... I didn't think there was alrady a function, obviously... and I'm using xl2007, btw....

It worked until I tried to use a named range. It gave me the #NAME? error...

I'm trying to use it as a worksheet function, so that I can choose 2 different cells, then determine the number of business days between them, populating another cell with that information. If I use a direct reference (Tables!A3:A12), it works...

Ok.. looking at another database that uses alot of named ranges, I found the answer....

[vba]
=Networkdays(D4,E4,Holidays)
[/vba]

Where:
D4= 2/1/2010
E4= 12/31/2010

and the answer is 233.

so... thanks for pointing me in the right direction!!

GComyn

[Bob Phillips]

For a named range in VBA you have to use

Range("Holidays")

[Bob Phillips]

Same with the cells

Range("D4").Value

[gcomyn]

right... but if you are not using it in vba, but on the sheet itself, then you can't use the "range" function.

that is where the function would be used (at least, where I am using it).

Thanks for the Help!

GComyn

[p45cal]

right... but if you are not using it in vba, but on the sheet itself, then you can't use the "range" function.

that is where the function would be used (at least, where I am using it).

Thanks for the Help!

GComyn

You didn't have a named range called Holidays, now you have set one up and all is well.. is that right?

[gcomyn]

Actually, I did have a named range, but couldn't figure out how to address it. I figured that out after you gave me the networkdays formula.

now, it works as I wanted it to, and I dont' have to worry about making sure the macros are working.