count amount of holidays in period of time, using vba excel

[ramhuis]

Hello, I am trying to find a way to return the amount of holidays in a certain period of time. This period of time is indicated with a beginning and ending date and I need to check the amount of holidays between these two dates. I have been trying to compare two arrays, the first one with the holidays (e.g. ArrayHoliday(01/01/2011, 06/01/2011, 07/03/2011....) and the second one with the dates of this period of time(e.g. ArrayPeriod(01/04/2011, 02/04/2011, 03/04/2011...). The problem is that I do not know how to fill this second one. For example the beginning date is the 01 of April and the end date is the 30 of April, how do I automaticly populate the array with all the dates between these two dates? And then how do I compare these two arrays and count the matches.... I have been looking in many sites and found many array examples but have not been able to apply them to this problem. Any help is very welcome, I am new with VBA and am using Excel 2010.

 

[Warship]

Welcome To The Board.
Would something like this work?

Sub countHolidays()
    Dim arrHolidays
    Dim HolidayCount
    Dim stDate As Date
    Dim enDate As Date
    Dim i
    arrHolidays = Array("1/1/2011", "4/13/2011", "4/28/2011", "5/13/2011")
    stDate = "4/1/2011"
    enDate = "4/30/2011"
    For Each i In arrHolidays
        If i >= stDate And i <= enDate Then
            HolidayCount = HolidayCount + 1
        End If
    Next i
    MsgBox HolidayCount
End Sub

 

[ramhuis]

Dear Warship,

Many thanks for your effective solution! I used it in a function to return the amount of holidays that match the holidays in a range.

 

[ramhuis]

In the end the code looks like this:

Function countHolidays(stDate As Date, enDate As Date) As Byte
Dim arrHolidays As Variant
Dim HolidayCount As Byte
Dim i As Variant

arrHolidays = Worksheets("DATA 1").Range("I4:I17").Value

For Each i In arrHolidays
If i >= stDate And i <= enDate Then
HolidayCount = HolidayCount + 1
End If
Next i
countHolidays = HolidayCount

End Function

 

[Jonmo1]

For a non VBA approach

Try

A1 = Start DAte
B1 = End Date
C1:C10 = list of holidays

=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),C1:C10,0))))

Do not change the A:A's, they have nothing to do with the location of your data.

Change only A1, B1 and C1:C10


Hope that helps.

 

[ramhuis]

Dear Jonmo 1,

For some reason it does not accept this function. I have tried INDEX and MATCH in other functions but I always get a message that there is an error in the function. I have replaced A1, B1 and C1-C10 as indicated but without result....

 

[Jonmo1]

Works for me.

Start Date in A1
End Date in B1
Holidays in C1:C10 (made up holidays of coarse)

Excel Workbook
	A	B	C
1	1/1/2011	4/26/2011	Holidays
2			10/3/2010
3	4		11/19/2010
4			12/12/2010
5			1/1/2011
6			2/15/2011
7			3/21/2011
8			4/18/2011
9			5/1/2011
10			6/10/2011
Sheet1

 

[ramhuis]

Hi Jonmo,

It worked, I am working with dutch & spanish excel, forgot to adjust the formula to dutch or spanish...

Many thanks for the tip!