Calculate Working Days Minus Holidays in VBA
June 17, 2008 – 9:50 am by JP
I was looking for a function that calculates working days, minus holidays, for a small project I’ve been working on. I decided to write one myself, but below I’ve also demonstrated an alternate way that uses the ATP (Analysis ToolPak) functions for VBA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | Function WorkDays(StartDate As Variant, EndDate As Variant) As Long
' adapted from:
' http://www.exceltip.com/st/Calculate_the_count_of_workdays_between_two_dates_using_VBA_in_Microsoft_Excel/523.html
' and http://support.microsoft.com/kb/97757
'
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
' also checks for holidays (added by Jimmy Pena 6/17/2008)
Dim lCounter As Long
Dim DaysCount As Long
Dim HolidaysList()
Dim ArrMember
Dim bWasFound As Boolean
bWasFound = False
StartDate = DateValue(StartDate)
EndDate = DateValue(EndDate)
' define holiday list as an array which we will check against
' each day; if match is found, do not increase workday count
'
' edit/add/delete as needed
' these are US Federal Holidays
HolidaysList = Array("1/1/2007", "1/15/2007", "2/19/2007", "5/28/2007", "7/4/2007", "9/3/2007", "10/8/2007", "11/12/2007", _
"11/22/2007", "12/25/2007", "1/1/2008", "1/21/2008", "2/18/2008", "5/26/2008", "7/4/2008", "9/1/2008", "10/13/2008", _
"11/11/2008", "11/27/2008", "12/25/2008")
For lCounter = StartDate To EndDate
If Weekday(lCounter, vbMonday) < 6 Then
For Each ArrMember In HolidaysList
If Format(ArrMember, "mm/dd/yyyy") = Format(lCounter, "mm/dd/yyyy") Then
bWasFound = True
Exit For
Else
bWasFound = False
End If
Next ArrMember
If bWasFound = False Then
DaysCount = DaysCount + 1
End If
End If
Next lCounter
WorkDays = DaysCount
End Function |
Here we create an array of holidays, then loop through the dates passed as arguments to the function to see if they are not weekend days. The inner loop checks to see if the date matches one of the holidays in the array variable. If both conditions are met (i.e. a weekday and not a holiday) we add to the count (DaysCount variable).
The Boolean variable is used as a flag inside the loop — if the date matches a holiday date, the flag is raised and the loop immediately exits. We check the flag after the loop is complete and add to the count if it was left False.
To use:
1 | MsgBox WorkDays("1/1/2008", "1/31/2008") |
Or since the function returns a Long value, assign the result to a variable
1 2 | Dim lCountOfDays As Long
lCountOfDays = WorkDays("1/1/2008", "1/31/2008") |
Here’s another way, using the ATP library for VBA. To set a reference, open the VBE and go to Tools > References and set a reference to “atpvbaen.xls”.
You may also need to go back to Excel and go to Tools > Add-Ins and select “Analysis ToolPak - VBA”.
Now you can use the NETWORKDAYS function directly in VBA like this:
1 2 3 4 5 6 7 | Dim HolidaysList()
HolidaysList = Array("1/1/2007", "1/15/2007", "2/19/2007", "5/28/2007", "7/4/2007", "9/3/2007", "10/8/2007", "11/12/2007", _
"11/22/2007", "12/25/2007", "1/1/2008", "1/21/2008", "2/18/2008", "5/26/2008", "7/4/2008", "9/1/2008", "10/13/2008", _
"11/11/2008", "11/27/2008", "12/25/2008")
MsgBox networkdays("1/1/2008", "1/31/2008", HolidaysList) |
Which should give you the same answer, and in my tests, at approximately the same speed.
Personally I like my way better, but that’s only because I worked on it and I hate throwing away old code. Plus it uses built-in functions in case you don’t want to add additional object libraries to your VBE. As you can see above, I already have a lot, so anything already built-in to VBA I can use is something I will prefer.
Enjoy,
JP
Print This Post
|
Email This Post
|
Permalink
|
Subscribe now!
Filed Under: Excel, UDF, VBA
Tags: ATP, holidays, networkdays, UDF, VBA
This post has 998 views since June 17, 2008 – 9:50 am.
3 Responses to “Calculate Working Days Minus Holidays in VBA”
Hi - down here in the southern hemisphere we run with different date formats ie day/month/year and I can not get this macro to calculate correctly.
I am trying to calculate the number of hours between when a quote was requested and when it was submitted - using 24 hours per day x total days - weekends - holidays.
I have been playing with both Workdays function and TimeDiff function VBA code but to no avail as yet. I have tried holiday dates in different formats in Sub.
eg Input data:
Date/time quote requested 5/02/2008 8:00 (i.e. in one field with format d/mm/yyyy h:mm)
Date/Time quote submitted 30/04/2008 17:00
NZ HolidaysList = Array(”1/01/2007″, “6/02/2007″, “6/04/2007″, “9/04/2007″, “25/04/2007″, “4/06/2007″, “22/10/2007″, “25/12/2007″, “26/12/2007″)
= 85.38 days - 24 days weekends - 4days holidays = 57.38 days, x24 = 1377 hours
Networkdays = 58 days, x 24 = 1392hours
With
Const ComeIn As Date = “0:00″
Const Leave As Date = “23:59″
TimeDiff = 1448 hours which has added more hours not subtracted for holidays or failed to find any holidays (which is what I thought it would do if could not match holidays with dates)?
I have also played with modified code from “Visual Basic Language Developer’s Handbook” by Ken Getz and Mike Gilbert and once again can not get calculating correctly. I do not want to use american date formats for my quote data as it has NZ users.
Please can you help.
By M White on Jul 26, 2008
I’ll check it out and let you know. It would probably help if you send me the full code you are using. Just use the contact form and paste in the code:
http://www.codeforexcelandoutlook.com/Contact.html
–JP
By JP on Jul 27, 2008
I just noticed that your work date range is in 2008, but the holidays list is in 2007. If that is the case, the NETWORKDAYS function won’t subtract the holidays from the total workdays.
If you can fix that, it should work. Let me know!
–JP
By JP on Jul 28, 2008