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

ramhuis

New Member
Joined
Apr 24, 2011
Messages
6
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome To The Board.
Would something like this work?
Code:
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
Works for me.

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


Excel Workbook
ABC
11/1/20114/26/2011Holidays
210/3/2010
3411/19/2010
412/12/2010
51/1/2011
62/15/2011
73/21/2011
84/18/2011
95/1/2011
106/10/2011
Sheet1
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,543
Messages
6,179,429
Members
452,914
Latest member
echoix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top